python - parsing interactive broker fundamental data

Question

I've successfully pulled data from IB using the api. It comes in XML format and it looks like this...

 <TotalRevenues currency="USD">
      <TotalRevenue asofDate="2017-12-31" reportType="TTM" period="12M">239176000000.000000</TotalRevenue>
      <TotalRevenue asofDate="2017-09-30" reportType="TTM" period="12M">229234000000.000000</TotalRevenue>
      <TotalRevenue asofDate="2017-06-30" reportType="TTM" period="12M">223507000000.000000</TotalRevenue>
</TotalRevenues>
   <DividendPerShares currency="USD">
      <DividendPerShare asofDate="2017-12-31" reportType="A" period="3M">0.630000</DividendPerShare>
      <DividendPerShare asofDate="2017-09-30" reportType="A" period="3M">0.630000</DividendPerShare>
      <DividendPerShare asofDate="2017-09-30" reportType="A" period="12M">2.400000</DividendPerShare>
   </DividendPerShares>
   <Dividends currency="USD">
      <Dividend type="CD" exDate="2018-02-09" recordDate="2018-02-12" payDate="2018-02-15" declarationDate="2018-02-01">0.630000</Dividend>
      <Dividend type="CD" exDate="2017-11-10" recordDate="2017-11-13" payDate="2017-11-16" declarationDate="2017-11-03">0.630000</Dividend>
      <Dividend type="CD" exDate="2017-08-10" recordDate="2017-08-14" payDate="2017-08-17" declarationDate="2017-07-02">0.630000</Dividend>
   </Dividends>
   <EPSs currency="USD">
      <EPS asofDate="2017-12-31" reportType="A" period="3M">3.920000</EPS>
      <EPS asofDate="2017-09-30" reportType="A" period="3M">2.090000</EPS>
      <EPS asofDate="2017-09-30" reportType="A" period="12M">9.270000</EPS>
   </EPSs>
</FinancialSummary>

I want to convert this information to CSV format in this format:

            total revenue report type     period rev dividendpershare period div
2017-12-31  239176000000     ttm           12m        0.630000          3m
2017-09-30   229234000000    ttm           12m        0.630000          3m

is there an easy way to do this?

Answer 1

You can try this:

import csv
import xmltodict

XML = """
<FinancialSummary>
    <TotalRevenues currency="USD">
        <TotalRevenue asofDate="2017-12-31" reportType="TTM" period="12M">239176000000.000000</TotalRevenue>
        <TotalRevenue asofDate="2017-09-30" reportType="TTM" period="12M">229234000000.000000</TotalRevenue>
        <TotalRevenue asofDate="2017-06-30" reportType="TTM" period="12M">223507000000.000000</TotalRevenue>
    </TotalRevenues>
    <DividendPerShares currency="USD">
        <DividendPerShare asofDate="2017-12-31" reportType="A" period="3M">0.630000</DividendPerShare>
        <DividendPerShare asofDate="2017-09-30" reportType="A" period="3M">0.630000</DividendPerShare>
        <DividendPerShare asofDate="2017-09-30" reportType="A" period="12M">2.400000</DividendPerShare>
    </DividendPerShares>
    <Dividends currency="USD">
        <Dividend type="CD" exDate="2018-02-09" recordDate="2018-02-12" payDate="2018-02-15" declarationDate="2018-02-01">0.630000</Dividend>
        <Dividend type="CD" exDate="2017-11-10" recordDate="2017-11-13" payDate="2017-11-16" declarationDate="2017-11-03">0.630000</Dividend>
        <Dividend type="CD" exDate="2017-08-10" recordDate="2017-08-14" payDate="2017-08-17" declarationDate="2017-07-02">0.630000</Dividend>
    </Dividends>
    <EPSs currency="USD">
        <EPS asofDate="2017-12-31" reportType="A" period="3M">3.920000</EPS>
        <EPS asofDate="2017-09-30" reportType="A" period="3M">2.090000</EPS>
        <EPS asofDate="2017-09-30" reportType="A" period="12M">9.270000</EPS>
    </EPSs>
</FinancialSummary>
"""


def write_to_csv(rows):
    header = 'date, total revenue, report type, period rev, dividendpershare, period div'.split(', ')
    with open('sample.csv', 'w', newline='') as fo:
        writer = csv.writer(fo)
        writer.writerow(header)
        writer.writerows(rows)


def main():
    d = xmltodict.parse(XML)
    root = d['FinancialSummary']
    total_revenue_list = root['TotalRevenues']['TotalRevenue']
    dividend_per_share_list = root['DividendPerShares']['DividendPerShare']
    rows = []
    for total_rev, dps in zip(total_revenue_list, dividend_per_share_list):
        row = [
            total_rev['@asofDate'],
            total_rev['#text'],
            total_rev['@reportType'],
            total_rev['@period'],
            dps['#text'],
            dps['@period']
        ]
        rows.append(row)

    write_to_csv(rows)


if __name__ == '__main__':
    main()

Which will produce a sample.csv like this:

date,total revenue,report type,period rev,dividendpershare,period div
2017-12-31,239176000000.000000,TTM,12M,0.630000,3M
2017-09-30,229234000000.000000,TTM,12M,0.630000,3M
2017-06-30,223507000000.000000,TTM,12M,2.400000,12M

This sample program is written in Python3, and used a third-party library named xmltodict, you can install it by pip install xmltodict.