Tradeking to Morningstar data conversion

I recently wanted to import my stock transaction portfolio from Ally/Tradeking to Morningstar which has stock ratings and research. It wasn’t quite straightforward, although I still appreciate the CSV import functionality in morningstar, despite not being perfect.

Here’s the script.

#expected format by morningstar
# Symbol,Quantity,Price,Action,TradeDate,Amount,Commission
# ABT,200,49.1475,Buy,8/31/2010,9836.5,7

#current output format by Ally/Tradeking
# "Symbol","Description","Qty","Underl.Stock","CostBasis","Avg Price","Price<sup>*</sup>","Change","Change<br />%","TotalG/L","MarketValue","","",
# "AGN","Allergan Plc","10",,"$2,448.25","$244.83","$173.75","-$1.03","-0.59","-$710.75","$1,737.50","",

import csv
output_columns = "Symbol,Quantity,Price,Amount"
with open('ally.csv', 'r') as ally, open('morningstar.csv','w') as mstar:
     portfolio = csv.reader(ally, delimiter=',', quotechar='"')
     mstar_csv = csv.writer(mstar, delimiter=",")
     header = next(portfolio)
     mstar_csv.writerow(output_columns.replace('"','').split(','))
     for holding in portfolio:
         holding_dict = dict(zip(header, holding))
         if holding_dict['Avg Price'] != "": #ignore summary and other rows that don't have a transaction price on them
             mstar_csv.writerow([
                holding_dict['Symbol'],
                holding_dict['Qty'],
                holding_dict['Avg Price'].replace("$","").replace(",",""),
                holding_dict['CostBasis'].replace("$","").replace(",","")
             ])

A few things to note,

Using csv reader makes reading and writing things much easier, instead of using split()
Using dict and zip instead of record[0] makes the code a lot more readable and maintainable - you are treating data like tables with column names - as opposed to arrays and indices. It also shields you against any changes in order of the source columns.

Many thanks to David Beazley’s Advanced Python course for some of these techniques. Highly recommended.