1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
|
import csv
from datetime import date, datetime
import os
# headers = ["Record Type", "Account/Portfolio", "Transaction Type",
# "Type of Security", "Security Id", "Clearing broker",
# "Primary broker", "Units", "Price per unit", "Gross amount",
# "Net amount", "Accrued Interest", "Broker Commission",
# "Misc. Expense broker", "Broker Postage",
# "SEC Fee", "Transfer Tax", "Trade Date", "Settlement Date",
# "Settlement Location", "Storage Location", "Registration",
# "Tax Lot Seq Num", "Tax Lot Qty", "Explanation", "Fed Cost"]
# headers = headers + ["Special Instruction " + str(i) for i in range(1,9)]
# headers = headers + ["Future Use"]
if os.name =='nt':
root = "//WDsentinel/share/Daily"
elif os.name == 'posix':
root = '/home/share/Daily'
def get_broker_codes():
with open(os.path.join(root, "broker_codes.csv")) as fh:
next(fh)
return dict([line.rstrip().split(",") for line in fh])
def list_trade_files():
return (f for f in os.listdir(os.path.join(root, str(date.today()))) \
if "trade" in f)
account_number = "602382.1"
print("HEADER|" + datetime.strftime(date.today(), "%Y%m%d"))
n_trades = 0
broker_codes = get_broker_codes()
for trade_file in list_trade_files():
with open(os.path.join(root, str(date.today()), trade_file)) as fh:
reader = csv.DictReader(fh)
for trade in reader:
n_trades += 1
tradedate = datetime.strftime(datetime.strptime(trade["Date"],"%m/%d/%Y"),
"%Y%m%d")
settledate = datetime.strftime(datetime.strptime(trade["Settle Date"],"%m/%d/%Y"),
"%Y%m%d")
for field in ["Notional", "Price", "Acc Int"]:
trade[field] = float(trade[field])
grossamount = trade["Notional"] * trade["Price"] / 100
accint = trade["Acc Int"] * trade["Notional"]/100
netamount = grossamount + accint
brokercode = "{0:08}".format(int(broker_codes[trade["Alias"]]))
tradestring = \
"DETAIL|{0}|{1}|{2}|{3}|{4}|{5}".format(account_number,
trade["Buy/Sell"].upper(),
"CUSIP" if trade["CUSIP"] else "ISIN",
trade["CUSIP"] + trade["ISIN"],
brokercode,
brokercode)
tradestring += \
"|{0:.4f}|{1:.8f}|{2:.2f}|{3:.2f}|{4:.2f}||||||".format(trade["Notional"],
trade["Price"],
grossamount,
netamount,
accint)
tradestring += \
"{0}|{1}|3|3|3||||||||||||||".format(tradedate,
settledate)
print(tradestring)
print("TRAILER|{0}".format(n_trades))
|
