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.

Complete Import CSV file before running this example.

Use: File, Export, CSV files, under Securities Account Transactions select Broker Securities, Save as Broker_Securities.csv and run the python script below

py Broker_Securities

Save as

# © flywire 2022, 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']

# Enable next line to remove zero balance lines 
# df = df[df.Deposit != 0]
df.rename(columns={'Note': 'Notes'}, inplace=True)
df[['Date', 'Description', 'Notes', '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 by removing # in add_split
  • remove zero balance lines by removing # near the bottom of main program
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