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
CAREFULLY - IT OVERWRITES FILES WITHOUT WARNING!

>py pp_security_splits.py Broker_Securities

Save as pp_security_splits.py

# © 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 pp_security_splits.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
    return


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'])
    add_split(
        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

Notes:

  • Short split records shown (for importing into GnuCash) but long records can be enabled
Date,Description,Note,Account,Deposit
2021-01-04,REA,T20210104689951-1,Bank:Broker Funds,-9880.86
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,9871.36
2021-02-01,RIO,T20210201523636-1,Bank:Broker Funds,-9912.53
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,9903.03
2021-03-01,WPL,T20210301378143-1,Bank:Broker Funds,-9998.02
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,9988.52
2021-03-29,WOW,T20210329345452-1,Bank:Broker Funds,-9998.18
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,9988.68
2021-04-26,CBA,T20210426460841-1,Bank:Broker Funds,-9983.1
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,9973.6
2021-05-24,WPL,T20210524608678-1,Bank:Broker Funds,8695.86
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,-8705.36
2021-05-24,ALL,T20210524356856-1,Bank:Broker Funds,-18690.8
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,18681.3
2021-06-21,NCM,T20210621600557-1,Bank:Broker Funds,-9986.65
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,9977.15