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
CAREFULLY - IT OVERWRITES OUTPUT FILE WITHOUT WARNING!
py pp_security_splits.py Broker_Securities
Save as pp_security_splits.py
# © 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 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']
)
# 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
Notes:
- 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
Date,Description,Notes,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