def db_update(self):
"""
Updates the respective tables with required data using the transactions
:return: na
"""
con = sqlite3.connect(MintDB.DB_FILEPATH)
with con:
cur = con.cursor()
for trans in reversed(self.trans):
rowout = []
valstr = ''
date_tr = pc.date_converter(str(trans['date']))
for head in self.DB_TRANS_H:
if head == 'date':
rowout.append(date_tr)
elif head == 'days':
days = (dt.date.today() - date_tr).days
rowout.append(days)
elif head == 'amount':
amount = float(trans['amount'].replace('$', '').replace(',', ''))
rowout.append(amount)
else:
rowout.append(str(trans[head]))
valstr += '?,'
cur.execute("INSERT INTO {} VALUES({})".format(self.DB_TABLENAME_TRANS, valstr[:-1]), rowout)
# update stock list based on transactions
if str(trans['fi']) == 'Scottrade' and \
(trans['category'] == 'Buy' or trans['category'] == 'Sell'):
bsplit = list(trans['merchant'].split(' '))
sym = str(bsplit[4]).upper()
shares = int(bsplit[1])
if bsplit[0] == 'Sold':
shares *= -1
price = float(bsplit[-1].replace(',', '').replace('$', ''))
date = pc.date_converter(trans['date'])
rowout = []
valstr = ''
for head in self.DB_STOCK_H:
rowout.append(eval(head))
valstr += '?,'
cur.execute("INSERT INTO {} VALUES({})".format(self.DB_TABLENAME_STOCK, valstr[:-1]), rowout)
for acc in self.accounts:
rowout = []
valstr = ''
for head in self.DB_ACC_H:
try:
rowout.append(acc[head])
except KeyError:
rowout.append('null')
valstr += '?,'
cur.execute("INSERT INTO {} VALUES({})".format(self.DB_TABLENAME_ACC, valstr[:-1]), rowout)
self.logger.info("Database update complete")
def get_date_sort(self):
self.set_fixed_info()
fixed_date_amt = {}
var_date_amt = {}
for tr in self.trans:
if tr['isIncome'] == 0:
date = pc.date_converter(tr['date'])
if tr['isFixed'] == 1:
fixed_date_amt.setdefault(date, 0)
fixed_date_amt[date] += round(float(tr['amount']), 2)
else:
var_date_amt.setdefault(date, 0)
var_date_amt[date] += round(float(tr['amount']), 2)
return fixed_date_amt, var_date_amt
def getTotalInvested(self):
# function that returns total invested into SCOTTRADE
total = float(0.0)
inf_total = float(0.0)
grow_total = float(0.0)
for tr in self.mp.trans:
if (tr['category'] == 'Transfer') and (tr['fi'] == 'Scottrade'):
total += tr['amount']
iat, gat, ssp = self.figo.capital_calculations(pc.date_converter(tr['date']), tr['amount'])
inf_total += iat
grow_total += gat
# total += float(tr['amount'].replace('$', '').replace(',', ''))
return total, inf_total, grow_total
def runTracker():
# get data from amazontracker.csv
data_csv = ap.getCsvData()
for data_row in data_csv:
url = data_row[0]
price_orig = float(data_row[2])
date_orig = pc.date_converter(data_row[1], 1)
logger.info('runTracker() for %s', str(data_row[3]))
if (date_today - date_orig).days <= 30:
price_now = ap.getPrice(url)
logger.info('%s -- $%s vs (now) $%s' % (str(data_row[3]), str(price_orig), str(price_now)))
if price_now < price_orig:
logger.info('Sending SMS for product %s', str(data_row[3]))
subject = str('Amazon_' + str(data_row[3]))
message = str('Price from $' + str(price_orig) + ' to $' + str(price_now))
ym.sendText(subject, message)
logger.info('DONE =runTracker()')
def trade_seller(self):
figo = FiGrowth()
figo.GROWTH_RATE = 0.20 # 20% growth
datenow = datetime.date.today()
con = sqlite3.connect(self.DB_FILE_PATH)
with con:
cur = con.cursor()
trade_data = cur.execute('select * from activestocks').fetchall()
for row in trade_data:
date_invested = pc.date_converter(row[1:][self.DB_STOCK_HEAD.index('Date')])
ip, gp, sp = figo.capital_calculations(date_invested, row[1:][self.DB_STOCK_HEAD.index('InvestAmt')])
current_value = row[1:][self.DB_STOCK_HEAD.index('CurrentAmt')]
sell_flag = ((datenow - date_invested).days > 2) and (current_value > gp)
if sell_flag:
cur.execute("DELETE FROM activestocks WHERE ID=?",(row[0],))
cur.execute("INSERT INTO soldstocks VALUES(NULL,?, ?, ?, ?, ?, ?, ?, ?, ? )", row[1:])
cur.execute("UPDATE soldstocks SET Date=? WHERE CurrentAmt=?", (datenow, current_value))
def get_stocks(self, where=''):
"""
Gets the stock trade from DB
:param where: Where clause for DB search
:return: stocks in dict format
"""
con = sqlite3.connect(MintDB.DB_FILEPATH)
with con:
cur = con.cursor()
cur.execute("SELECT * FROM {} ".format(self.mdb.DB_TABLENAME_STOCK) + where)
stockdb = cur.fetchall()
stocks = {}
for st in stockdb:
sym = str(st[self.mdb.DB_STOCK_H.index('sym')])
shares = int(st[self.mdb.DB_STOCK_H.index('shares')])
price = float(st[self.mdb.DB_STOCK_H.index('price')])
date = pc.date_converter(st[self.mdb.DB_STOCK_H.index('date')])
stocks.setdefault(sym, {}).setdefault('shares', []).append(shares)
stocks.setdefault(sym, {}).setdefault('price', []).append(price)
stocks.setdefault(sym, {}).setdefault('date', []).append(date)
return stocks