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)))
