def cfd_process(): session = get_session() for i in ( session.query(RawData) .filter(sqlalchemy.or_(RawData.category == RawData.CAT_TRADE, RawData.category == RawData.CAT_INDEX)) .order_by(RawData.import_id, RawData.ref_date) ): # OK, so this will essentially be a closing trade. Or part of one. # Check if there is already an open position with this broker ref # TODO # If so, Check if there's more than one...if so things could be messy... # Is there any way to work out exact quantities in this case??? # I'm going to assume there's no legging into trades for now (and I don't think there is?) # There's no way to defnitively process if there is (without cross referencing # other data files). For now can just assume first commission is on trade open, # and all subsequent commissions are as part of closing a tranche. q = session.query(StockPosition).filter(StockPosition.broker_ref == i.broker_ref) pos = q.first() if pos is None: # Create new Position, and open/close activities pos = new_position(session, i) else: # update quantities/activities in the existing position add_to_position(session, i, pos) session.commit()
def categorise(): session = get_session() for i in session.query(RawData): if i.type == "DEAL": if (re.match(r'Australia\s*200', i.description, re.IGNORECASE)): i.category = RawData.CAT_INDEX else: i.category = RawData.CAT_TRADE elif i.type == "DEPO" and "BPAY" in i.description.upper(): i.category = RawData.CAT_TRANSFER elif i.type == "WITH" and "eft payment sent" in i.description.lower(): i.category = RawData.CAT_TRANSFER elif ( i.type == "EXCHANGE" or "ASX FEE" in i.description.upper() or re.search(r'Transfer from.*to.*at', i.description, re.IGNORECASE)): i.category = RawData.CAT_XFEE elif (i.type == "WITH" and "LONG INT" in i.description.upper()): i.category = RawData.CAT_INTEREST elif (i.type == "DEPO" and "SHORT INT" in i.description.upper()): i.category = RawData.CAT_INTEREST elif (i.type == "WITH" and " COMM " in i.description.upper()): i.category = RawData.CAT_COMM elif (i.type == "WITH" and " CRPREM " in i.description.upper()): i.category = RawData.CAT_RISK elif ( i.type == "DIVIDEND" or re.match(r'DVD[A-Z]', i.description, re.IGNORECASE)): i.category = RawData.CAT_DIVIDEND else: i.category = RawData.CAT_UNKNOWN session.commit()
def csv_export(start_date, end_date, dirname): export = ExportData(dirname) session = get_session() total_profit = D(0) count_trades = 0 interest_long = D(0) interest_short = D(0) commission= D(0) other_comm= D(0) xfee= D(0) dividends= D(0) deposit= D(0) withdraw= D(0) final_balance = D(0) unknown = D(0) if start_date and end_date: q = session.query(RawData).filter(RawData.ref_date>=start_date,RawData.ref_date<=end_date) elif start_date: q = session.query(RawData).filter(RawData.ref_date>=start_date) elif end_date: q = session.query(RawData).filter(RawData.ref_date<=end_date) else: q = session.query(RawData) q = q.order_by(RawData.import_id, RawData.ref_date) # # First export "cash" type transactions (dividends, interest, etc) # for i in q: if i.category == RawData.CAT_TRADE or i.category == RawData.CAT_INDEX: total_profit += i.amount count_trades += 1 elif i.category == RawData.CAT_TRANSFER: if i.type == "DEPO": deposit += i.amount elif i.type == "WITH": withdraw += i.amount elif i.category == RawData.CAT_XFEE: xfee += i.amount elif i.category == RawData.CAT_INTEREST: if i.type == "DEPO": interest_short += i.amount export.shortint(i) elif i.type == "WITH": interest_long += i.amount export.longint(i) elif i.category == RawData.CAT_DIVIDEND: dividends += i.amount export.div(i) elif i.category == RawData.CAT_COMM: commission += i.amount elif i.category == RawData.CAT_RISK: other_comm += i.amount else: unknown += i.amount export.unknown(i) final_balance += i.amount # # Now export trades # if start_date and end_date: q = session.query(StockTrade).filter(StockTrade.exit_date>=start_date,StockTrade.exit_date<=end_date) elif start_date: q = session.query(StockTrade).filter(StockTrade.exit_date>=start_date) elif end_date: q = session.query(StockTrade).filter(StockTrade.exit_date<=end_date) else: q = session.query(StockTrade) q = q.order_by(StockTrade.exit_date, StockTrade.import_id) for i in q: export.trade(i) # # Print summary # print("\nCFD EXPORT SUMMARY") if not start_date and not end_date: print("[entire data set]\n") else: datestr = "" if start_date: datestr += "FROM " + str(start_date) + " " if end_date: datestr += "TO " + str(end_date) datestr += '\n' print(datestr) print("Total profit/loss: $%s" % (str(total_profit),)) print("Number of trades: ", count_trades) print("\nInterest paid on long positions: $%s\n" "Interest earned on short positions: $%s\n" % (str(interest_long), str(interest_short))) print("Commissions: $%s\n" "Guaranteed stop loss commissions: $%s\n" % (str(commission), str(other_comm))) print("ASX Exchange data fees: $%s\n\n" "Total dividend adjustments: $%s\n" % (str(xfee), str(dividends))) print("Deposits: $%s\nWithdrawals: $%s" % (str(deposit), str(withdraw))) print("Unknown: $%s\n\nFINAL BALANCE: $%s" % (str(unknown), str(final_balance))) export.clean_up()
def legacy_summary(start_date, end_date): session = get_session() total_profit = D(0) count_trades = 0 interest_long = D(0) interest_short = D(0) commission= D(0) other_comm= D(0) xfee= D(0) dividends= D(0) deposit= D(0) withdraw= D(0) final_balance = D(0) unknown = D(0) if start_date and end_date: q = session.query(RawData).filter(RawData.ref_date>=start_date,RawData.ref_date<=end_date) elif start_date: q = session.query(RawData).filter(RawData.ref_date>=start_date) elif end_date: q = session.query(RawData).filter(RawData.ref_date<=end_date) else: q = session.query(RawData) for i in q: if i.category == RawData.CAT_TRADE: total_profit += i.amount count_trades += 1 elif i.category == RawData.CAT_TRANSFER: if i.type == "DEPO": deposit += i.amount elif i.type == "WITH": withdraw += i.amount elif i.category == RawData.CAT_XFEE: xfee += i.amount elif i.category == RawData.CAT_INTEREST: if i.type == "DEPO": interest_short += i.amount elif i.type == "WITH": interest_long += i.amount elif i.category == RawData.CAT_DIVIDEND: dividends += i.amount elif i.category == RawData.CAT_COMM: commission += i.amount elif i.category == RawData.CAT_RISK: other_comm += i.amount else: unknown += i.amount final_balance += i.amount print("CFD LEGACY SUMMARY\n") print("Total profit/loss: $%s" % (str(total_profit),)) print("Number of trades: ", count_trades) print("\nInterest paid on long positions: $%s\n" "Interest earned on short positions: $%s\n" % (str(interest_long), str(interest_short))) print("Commissions: $%s\n" "Guaranteed stop loss commissions: $%s\n" % (str(commission), str(other_comm))) print("ASX Exchange data fees: $%s\n\n" "Total dividend adjustments: $%s\n" % (str(xfee), str(dividends))) print("Deposits: $%s\nWithdrawals: $%s" % (str(deposit), str(withdraw))) print("Unknown: $%s\n\nFINAL BALANCE: $%s" % (str(unknown), str(final_balance)))