def get_latest_price(self, ticker):
token = environ['IBKR_TOKEN']
queryId = environ['IBKR_QUERY_ID']
try:
response = client.download(token, queryId)
except client.ResponseCodeError as e:
if e.code == '1018':
sleep(10)
response = client.download(token, queryId)
else:
raise e
statement = parser.parse(response)
for position in statement.FlexStatements[0].OpenPositions:
if position.symbol.rstrip('z') == ticker:
price = D(position.markPrice)
timezone = tz.gettz("Europe/Zurich")
time = datetime.combine(
position.reportDate,
datetime.min.time()).astimezone(timezone)
return source.SourcePrice(price, time, position.currency)
return None
def test_request_statement(self, mock_requests_get: Mock):
output = client.download(
token="DEADBEEF",
query_id="0987654321",
)
self.assertIsInstance(output, bytes)
self.assertEqual(
mock_requests_get.call_args_list,
[
call(
client.REQUEST_URL,
params={
"v": "3",
"t": "DEADBEEF",
"q": "0987654321"
},
headers={"user-agent": "Java"},
timeout=5,
),
call(
client.STMT_URL,
params={
"v": "3",
"t": "DEADBEEF",
"q": "1234567890"
},
headers={"user-agent": "Java"},
timeout=5,
),
],
)
response = parser.parse(BytesIO(output))
self.assertIsInstance(response, Types.FlexQueryResponse)
def extort(self, stream: typing.Union[typing.IO[typing.AnyStr], str]):
"""Extract as much information as possible from the workbook"""
for statement in parser.parse(stream).FlexStatements:
for record in self.extract_cash(statement):
yield dataclasses.asdict(record)
for trade in self.extract_trades(statement):
yield dataclasses.asdict(trade)
def loadIBFlex(self, filename):
try:
report = parser.parse(filename)
except Exception as e:
logging.error(g_tr('StatementLoader', "Failed to parse Interactive Brokers flex-report") + f": {e}")
return False
for statement in report.FlexStatements:
self.loadIBStatement(statement)
return True
def extract(self, credsfile, existing_entries=None):
# the actual processing of the flex query
# get the IBKR creentials ready
try:
with open(credsfile.name, 'r') as f:
config = yaml.safe_load(f)
token = config['token']
queryId = config['queryId']
except:
warnings.warn('cannot read IBKR credentials file. Check filepath.')
return[]
# get prices of existing transactions, in case we sell something
#priceLookup = PriceLookup(existing_entries, config['baseCcy'])
if self.filepath == None:
# get the report from IB. might take a while, when IB is queuing due to
# traffic
try:
# try except in case of connection interrupt
# Warning: queries sometimes take a few minutes until IB provides
# the data due to busy servers
response = client.download(token, queryId)
statement = parser.parse(response)
except:
warnings.warn('could not fetch IBKR Statement. exiting.')
# another option would be to try again
return[]
assert isinstance(statement, Types.FlexQueryResponse)
else:
print('**** loading from pickle')
with open(self.filepath,'rb') as pf:
statement = pickle.load(pf)
# convert to dataframes
poi=statement.FlexStatements[0] # point of interest
reports=['CashReport','Trades','CashTransactions'] # relevant items from report
tabs={report:pd.DataFrame([{key:val for key,val in entry.__dict__.items()}
for entry in poi.__dict__[report]])
for report in reports}
# get single dataFrames
ct=tabs['CashTransactions']
tr=tabs['Trades']
cr=tabs['CashReport']
# throw out IBKR jitter, mostly None
ct.drop(columns=[col for col in ct if all(ct[col].isnull())],inplace=True)
tr.drop(columns=[col for col in tr if all(tr[col].isnull())],inplace=True)
cr.drop(columns=[col for col in cr if all(cr[col].isnull())],inplace=True)
transactions = self.Trades(tr) + self.CashTransactions(ct) + self.Balances(cr)
return transactions
def extract(self, file, existing_entries):
with open(file.name, 'r') as f:
config = yaml.safe_load(f)
token = config['token']
queryId = config['queryId']
priceLookup = PriceLookup(existing_entries, config['baseCcy'])
response = client.download(token, queryId)
statement = parser.parse(response)
assert isinstance(statement, Types.FlexQueryResponse)
transactions = []
for trx in statement.FlexStatements[0].CashTransactions:
existingEntry = None
if CashAction.DIVIDEND == trx.type or CashAction.WHTAX == trx.type:
existingEntry = next((t for t in transactions if t['date'] == trx.dateTime), None)
if existingEntry:
if CashAction.WHTAX == trx.type:
existingEntry['whAmount'] = trx.amount
else:
existingEntry['amount'] = trx.amount
existingEntry['description'] = trx.description
existingEntry['type'] = trx.type
else:
if CashAction.WHTAX == trx.type:
amount = 0
whAmount = trx.amount
else:
amount = trx.amount
whAmount = 0
transactions.append({
'date': trx.dateTime,
'symbol': trx.symbol,
'currency': trx.currency,
'amount': amount,
'whAmount': whAmount,
'description': trx.description,
'type': trx.type
})
result = []
for trx in transactions:
if trx['type'] == CashAction.DIVIDEND:
asset = trx['symbol'].replace('z', '')
payDate = trx['date'].date()
totalDividend = trx['amount']
totalWithholding = -trx['whAmount']
totalPayout = totalDividend - totalWithholding
currency = trx['currency']
_, rows = query.run_query(
existing_entries,
options.OPTIONS_DEFAULTS,
'select sum(number) as quantity, account where currency="' + asset + '" and date<#"' + str(payDate) + '" group by account;')
totalQuantity = D(0)
for row in rows:
totalQuantity += row.quantity
remainingPayout = totalPayout
remainingWithholding = totalWithholding
for row in rows[:-1]:
myAccount = row.account
myQuantity = row.quantity
myPayout = round(totalPayout * myQuantity / totalQuantity, 2)
remainingPayout -= myPayout
myWithholding = round(totalWithholding * myQuantity / totalQuantity, 2)
remainingWithholding -= myWithholding
result.append(self.createSingle(myPayout, myWithholding, myQuantity, myAccount, asset, currency, payDate, priceLookup, trx['description']))
lastRow = rows[-1]
result.append(self.createSingle(remainingPayout, remainingWithholding, lastRow.quantity, lastRow.account, asset, currency, payDate, priceLookup, trx['description']))
return result
import pandas as pd
from configparser import ConfigParser
config = ConfigParser()
config.read('/Users/joan/PycharmProjects/ThetaTrader/config.ini')
ibkr_flex_token = config.get('api_keys', 'ibkr_flex_token')
query_id = '524422'
#
# response = client.download(ibkr_flex_token, query_id)
# myfile = open("../../db/flex.xml", "w")
# myfile.write(response.decode())
# print(response[:215])
response = parser.parse(open("../../db/flex.xml", "r"))
print(response)
# stmt = response.FlexStatements[0]
l = []
for i in range(len(response.FlexStatements)):
stmt = response.FlexStatements[i]
l.append({
'Date': stmt.ChangeInNAV.fromDate,
'startingValue': stmt.ChangeInNAV.startingValue
})
df = pd.DataFrame(l)
def parse_file(file_name):
"""Wrapper callback for file cache"""
return parser.parse(file_name)