I wrote the following query function that works fine / accomplishes the
goal. However, being new to web2py, Python, etc., I'm wondering if I am
missing much smarter/better ways to accomplish the same outcome -- so as to
better guide my future efforts.
Thanks in advance for the advice.
Table being queried;
db.define_table('trades',
Field('account_id', db.accounts, required=True,
notnull=True, label="Brokerage Account"),
Field('trxn_date', 'date', required=True, notnull=True,
label="Transaction Date"),
Field('trxn_type', 'string', required=True, label="Buy or
Sell"),
Field('risk_amt', 'double', required=True, notnull=True,
label="Risk Amount"),
Field('symbol', 'string', required=True, notnull=True,
label="Securities Symbol"),
Field('share_price', 'double', required=True,
label="Purchase Price Per Share"),
Field('share_qty', 'double', required=True, label="Share
Quantity"))
Goal of query:
I want to get a list of all open positions -- i.e. if 100 shares of a
security is bought and 100 is sold, there's no open position; return
nothing. If 100 is bought and 99 is sold, there is an open position...
def get_open_positions(account_id=0L):
open_positions = []
#Get a list of all symbols for the account
all_symbols_in_acct = db(db.trades.account_id ==
account_id).select(db.trades.symbol, groupby=db.trades.symbol)
#Sum up all the buys and sells for each symbol
#e.g. Buy 100 FB, Sell 100 FB, Buy 100 AMZN, etc..
for symbol_row in all_symbols_in_acct:
#Get Sum of buys for symbol
qry = ((db.trades.account_id == account_id) &
(db.trades.symbol == symbol_row.symbol) &
(db.trades.trxn_type == "Buy"))
sum = db.trades.share_qty.sum()
share_qty_purchased = db(qry).select(sum).first()[sum]
share_qty_purchased = share_qty_purchased if share_qty_purchased !=
None else 0.0
#Get Sum of sells for symbol
qry = ((db.trades.account_id == account_id) &
(db.trades.symbol == symbol_row.symbol) &
(db.trades.trxn_type == "Sell"))
sum = db.trades.share_qty.sum()
share_qty_sold = db(qry).select(sum).first()[sum]
share_qty_sold = share_qty_sold if share_qty_sold != None else 0.0
#If the buys don't match the sells, there's open positions
#Return the related trade rows
if share_qty_purchased != share_qty_sold:
trxn_total = (db.trades.share_qty * db.trades.share_price).sum()
qry = ((db.trades.account_id == account_id) &
(db.trades.symbol == symbol_row.symbol) &
(db.trades.trxn_type == "Buy"))
buys_trxn_total = db(qry).select(trxn_total).first()[trxn_total]
buys_trxn_total = buys_trxn_total if buys_trxn_total != None
else 0.0
qry = ((db.trades.account_id == account_id) &
(db.trades.symbol == symbol_row.symbol) &
(db.trades.trxn_type == "Sell"))
sells_trxn_total =
db(qry).select(trxn_total).first()[trxn_total]
sells_trxn_total = sells_trxn_total if sells_trxn_total != None
else 0.0
shares_open = (share_qty_purchased-share_qty_sold)
if shares_open > 0:
#A Long Position
avg_cost = (buys_trxn_total/share_qty_purchased)
else:
#A Short Position
avg_cost = (sells_trxn_total/share_qty_purchased)
position = {'symbol': symbol_row.symbol,
'shares': shares_open,
'avg_cost': avg_cost}
open_positions.append(position)
return open_positions
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
