Import CSV Files to Create Portfolio
TLDR; Install application, create file with deposit and security accounts, export/import broker history as csv, tweak settings url for price updates, add taxonomies for reporting, export/import dividends as csv. Go!
Landing page
It is assumed you’ve been through the Guide on getting started tutorial so Portfolio Performance is installed (see Installing the application), you have some familiarity with it and the landing page is open (if the landing page was closed then reopen it with Help, Welcome).
Create a new file
Note: Portfolio contains at least two accounts, Deposit account with money and Securities account with shares (see Accounts for details).
From the Landing page click the Create a new file link or use File, New:
- Choose the base currency Australian Dollar (AUD)
- Add securities and reference accounts
eg Securities Account: Broker Securities, Reference Account: Broker Funds
Fund accounts
In this tutorial there is a buy every four weeks for eight periods and one sell. Deposit $60,000 into the Broker Funds account on 1/1/21.
Under Accounts, Deposit Accounts:
- select the account to add money to
- click on the right side “+” sign and then click on “Deposit” (or “Transfer”)
- add opening balance(s) to Deposit Account(s)
Export Broker Share Portfolio
Save the following CSV file exported from the broker’s account as DemoPortfolio.csv
Trade Date,Settlement,Action,Reference,Code,Name,Units,Average Price,Consideration,Brokerage,Total
04/01/21,06/01/21,Buy,T20210104689951-1,REA,REA Group Ltd,64,154.24,9871.36,9.50,9880.86
01/02/21,03/02/21,Buy,T20210201523636-1,RIO,RIO Tinto Ltd,89,111.27,9903.03,9.50,9912.53
01/03/21,03/03/21,Buy,T20210301378143-1,WPL,Woodside Petroleum Ltd,397,25.16,9988.52,9.50,9998.02
29/03/21,31/03/21,Buy,T20210329345452-1,WOW,Woolworths Group Ltd,247,40.44,9988.68,9.50,9998.18
26/04/21,28/04/21,Buy,T20210426460841-1,CBA,Commonwealth Bank of Australia,112,89.05,9973.60,9.50,9983.10
24/05/21,26/05/21,Sell,T20210524608678-1,WPL,Woodside Petroleum Ltd,397,21.88,8686.36,9.50,8695.86
24/05/21,26/05/21,Buy,T20210524356856-1,ALL,Aristocrat Leisure Ltd,459,40.70,18681.30,9.50,18690.80
21/06/21,23/06/21,Buy,T20210621600557-1,NCM,Newcrest Mining Ltd,383,26.05,9977.15,9.50,9986.65
Import Broker Share Portfolio
File, Import, CSV files, brings up an open file dialogue.
- Select DemoPortfolio.csv
Five types of data are supported: Account Transactions, Portfolio Transactions, Securities, Historical Quotes and Securities Account and each type has a different set of minimum fields.
- Type of data for this tutorial is Portfolio Transactions
then accept Delimiter and Encoding
Portfolio Performance uses the following definitions:
- Value: final value of the booking, ie the credit or debit
- Gross Amount: value before taxes and fees - in the currency of the security
- Map matching fields with a double-click and set format where required:
Trade Date → Date, Settlement, Action → Type, Reference → Note, Code → Ticker Symbol, Name → Security Name, Units → Shares, Average Price, Consideration, Brokerage → Fees, Total → Value
Hint: If format is likely to be repeated use the gear icon to Save current configuration.
- Go to Next screen and Finish
Note: This will likely only fill data on the Security Master Data tab. Other tabs can be important, eg Historical Quotes for change in value and Taxonomies for reporting.
New securities created by CSV import have no Historical Quotes Quote Feed Provider set so quotes are not updated by default (see #2333). To fix:
- View, All Securities, Right-click on each security, Edit, Historical Quotes and set:
- Provider to Yahoo Finance (or directly edit the data file)
- Exchange to Australian Stock Exchange (.AX) to change Symbol to Yahoo format by appending the exchange code, for example, .AX for ASX exchange
Get Dividend CSV File
There are various ways of getting a csv file with dividends. In this example bank statements are imported into a cashbook and a csv file is exported periodically with dividends.
Save the following CSV file exported from the data source as DemoDividends.csv
Paid,Code,Shares,Dividend,Franking,Total
23/03/21,REA,64,37.76,16.19,53.95
15/04/21,RIO,89,460.23,197.24,657.47
2/07/21,ALL,459,68.85,29.51,98.36
Import Dividend Transactions
File, Import, CSV files, brings up an open file dialogue.
- Select DemoDividends.csv
- Type of data for this tutorial is Account Transactions
then accept Delimiter and Encoding
Value field will vary depending on tax treatments. In this example dividend Value is the Dividend deposited into the bank account and Franking (tax) credit.
- Map matching fields with a double-click and set format where required (identical field names are matched automatically eg Shares):
Paid → Date, Code → Ticker Symbol, Shares → Shares, Dividend, Franking, Total → Value
Hint: If format is likely to be repeated use the gear icon to Save current configuration.
- Check Account Transactions and Finish
Use the File
Refer back to the Guide on getting started tutorial to use the application.
Please help keeping this tutorial up to date by editing it.
Thank you so much for your help.
