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 __init__(self, from_bank=True):
self.projected_balance_amt = {}
lh = loghandler("BalanceProjection")
self.logger = lh.getLogger()
self.pc = PayCheck()
self.cc, self.bank = self.get_cc_data(from_bank)
self.projected_balance_amt["chase"] = self.estimate_bank_bal(bankName="chase")
self.projected_balance_amt["bofa"] = self.estimate_bank_bal(bankName="bofa")
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
class AutoBankBalance:
def __init__(self, from_bank=True):
self.projected_balance_amt = {}
lh = loghandler("BalanceProjection")
self.logger = lh.getLogger()
self.pc = PayCheck()
self.cc, self.bank = self.get_cc_data(from_bank)
self.projected_balance_amt["chase"] = self.estimate_bank_bal(bankName="chase")
self.projected_balance_amt["bofa"] = self.estimate_bank_bal(bankName="bofa")
def get_cc_data(self, from_bank):
"""
Gets the credit card data
:param from_bank: If True, will load data from Mint server, else from local DB file
:return: credit card data in dict format and Chase bank info
"""
mp = MintPickle(update_db=from_bank)
accounts = mp.get_accounts()
cc = {}
bank = {}
for acc in accounts:
if str(acc["accountType"]) == "credit":
# BR card
if str(acc["fiName"]) == "Banana Republic Credit Card":
cc["br"] = copy.deepcopy(acc)
cc["br"]["payAcc"] = "bofa"
# Capital one card
elif str(acc["fiName"]) == "Capital One Credit Card":
cc["capone"] = copy.deepcopy(acc)
cc["capone"]["payAcc"] = "chase"
# Chase
elif str(acc["fiName"]) == "Chase Bank":
cc["chase"] = copy.deepcopy(acc)
cc["chase"]["payAcc"] = "chase"
# Discover card
elif str(acc["fiName"]) == "Discover Card":
cc["disc"] = copy.deepcopy(acc)
cc["disc"]["payAcc"] = "bofa"
elif str(acc["accountType"]) == "bank":
if str(acc["fiName"]) == "Bank of America":
if str(acc["name"]) == "BofA Core Checking":
bank["bofa"] = copy.deepcopy(acc)
elif str(acc["fiName"]) == "Chase Bank":
if str(acc["name"]) == "CHASE CHECKING":
bank["chase"] = copy.deepcopy(acc)
return cc, bank
def card_data(self, cat, type="chase"):
"""
Returns specific data in list format for all cards
:param cards: if any type of specific cards to call back
:param cat: key in credit cards data
:return: list format of specific data for all cards
"""
data = {}
for card in self.cc:
if self.cc[card]["payAcc"] == type:
data.setdefault(card, self.cc[card][cat])
return data
def estimate_bank_bal(self, bankName):
"""
Calculates the estimated balance at end
- calculates num of pays till end of month
- calculates dues on credit cards which are less than balance due, else it was paid earlier - (not true always\
can be paid off, but if a purchase exceeds (due-bal), it will be counted again. :/
- Removes RENT from the remaining
:return: total remaining balance by month end
"""
RENT = 0
PAY_TO_ACC = 0
SCOTTRADE = 0
if bankName == "chase":
PAY_TO_ACC = 1200 # bi
elif bankName == "bofa":
PAY_TO_ACC = 1530 # bi
RENT = 1550
SCOTTRADE = 300
num_pay = self.pc.remaining_paychecks_this_month()
bal_est = float(self.bank[bankName]["currentBalance"]) + PAY_TO_ACC * num_pay
pay_dues = self.card_data(cat="dueAmt", type=bankName)
bal_due = self.card_data(cat="currentBalance", type=bankName)
credit_due = 0
for idx, val in enumerate(pay_dues.values()):
if val <= bal_due.values()[idx]:
credit_due += val
bal_est = bal_est - credit_due - RENT - SCOTTRADE
return bal_est
def overdraft(self, bankName):
if self.projected_balance_amt[bankName] > 200:
return False
else:
return True
def send_mail(self, bankName):
ym = YmailSendText()
subj = "BalanceSheet"
msg_body = (
bankName.swapcase()
+ "\n Now bal = $"
+ str(self.bank["chase"]["currentBalance"])
+ "\n Est bal = $ "
+ str(self.projected_balance_amt["chase"])
)
ym.sendText(subj, msg_body)
def send_tweet(self, bankName):
bot = TweetBot()
msg_body = (
bankName.swapcase()
+ "\n Now bal = $"
+ str(self.bank["chase"]["currentBalance"])
+ "\n Est bal=$ "
+ str(self.projected_balance_amt["chase"])
)
bot.send_tweet(msg_body)
