Security Account Export CSV with Itemised Transaction Costs

Using: File, Export CSV, Deposit Account - unfortunately only shows total Value without itemised Brokerage and Fees. Can I export itemised costs for all transactions? I want to import them into my cashbook and this is the only place that information is held.

Python script to reformat PP export of security account to financial account splits, ready for import to cashbook program.

Example uses: Import CSV file

Use: File, Export CSV, Security Account and run the python script below

>py Broker_Securities

Save as

# © flywire 2021, CC BY-SA
'''Split Portfolio-Performance Security Account export into financial accounts.'''

from sys import argv
import collections
import numpy as np
import pandas as pd

COA = collections.OrderedDict()
COA['BROKER'] = 'Bank:Broker Funds'
COA['FEE'] = 'Expense:Brokerage'
COA['TAX'] = 'Expense:Tax'
COA['SHARE'] = 'Asset:Shares'

# py file.csv
FILENAME = argv[1]

def expand_df(df, n):
    # insert blank rows
    tmp_index = pd.RangeIndex(len(df) * n)
    tmp_df = pd.DataFrame(np.nan, index=tmp_index, columns=df.columns)
    ids = np.arange(len(df)) * n
    tmp_df.loc[ids] = df.values
    return tmp_df

def add_split(i, j, amount, coa):
    # Enable next line for full record
    # df.loc[i+j] = df.loc[i]
    df.loc[i + j, 'Deposit'] = amount
    df.loc[i + j, 'Account'] = coa

df = pd.read_csv(FILENAME + '.csv')
# Fix csv numbers
df['Date'] = [df['Date'][i][:10] for i in df.index]
df['Value'] = df['Value'].replace({'\$': '', ',': ''}, regex=True).astype(float)
df['Fees'] = df['Fees'].replace({'\$': '', ',': ''}, regex=True).astype(float)
df['Taxes'] = df['Taxes'].replace({'\$': '', ',': ''}, regex=True).astype(float)

df['Account'] = COA['BROKER']
df['Deposit'] = df['Value'] * -1
df['Description'] = [df['Ticker Symbol'][i][:-3] for i in df.index]

df = expand_df(df, len(COA))

for i in range(0, len(df), len(COA)):
    add_split(i, 1, df.loc[i, 'Fees'], COA['FEE'])
    add_split(i, 2, df.loc[i, 'Taxes'], COA['TAX'])
        i, 3, df.loc[i, 'Value'] - df.loc[i, 'Fees'] - df.loc[i, 'Taxes'], COA['SHARE']
df[['Date', 'Description', 'Note', 'Account', 'Deposit']].to_csv(
    FILENAME + '_split.csv', index = False

Sample Output: Broker_Securities_split.csv


  • Short split records shown (for importing into GnuCash) but long records can be enabled
2021-01-04,REA,T20210104689951-1,Bank:Broker Funds,-9880.86
2021-02-01,RIO,T20210201523636-1,Bank:Broker Funds,-9912.53
2021-03-01,WPL,T20210301378143-1,Bank:Broker Funds,-9998.02
2021-03-29,WOW,T20210329345452-1,Bank:Broker Funds,-9998.18
2021-04-26,CBA,T20210426460841-1,Bank:Broker Funds,-9983.1
2021-05-24,WPL,T20210524608678-1,Bank:Broker Funds,8695.86
2021-05-24,ALL,T20210524356856-1,Bank:Broker Funds,-18690.8
2021-06-21,NCM,T20210621600557-1,Bank:Broker Funds,-9986.65