Import CSV file

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.

PP-Import-CSV-Mapping

  • 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

PP-CSV-Import-Account-Transactions-Distributions1

  • 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.

PP-CSV-Import-Account-Transactions-Distributions2

  • 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.

8 Likes

There could be more surprises here:

and here: Einlieferung und Kauf von Wertpapieren in einer Datei beim CSV-Import
I hope DeepL Übersetzer: Der präziseste Übersetzer der Welt is helpful to understand.

1 Like

Thank you for the extra detail about csv importing issues, even if the translation makes it a bit difficult to understand.

Seems import csv file using Type of data as Securities account is okay. Dividends go into my bank account which is uploaded to my cashbook. Hopefully, an automated process could upload that to PP.

This process didn’t change Historical Quotes Quote Feed Provider to Yahoo Finance AU so I appended .AX to each security code and used Yahoo Finance (Adjusted Close).

  1. Can the Historical Quotes Quote Feed Provider be changed to Yahoo Finance AU? (non)help
  2. Can a default Quote Feed Provider be set instead of clicking on each security?

[The wiki needs updating based on the reply.]

I did not find a general suggestion thread for CSV Imports.
So here one issue I come across:
image

The format of numbers currently only identifies
0.000,00
0,000.00
0’000.00
but 0 000,00 not yet.

This new format would be useful for me at least as it then always just imports 1 if I use 0.000,00, but the value was 1 000.

This part of the forum is English. Can you add an English translation under your post?

The issue seems to be not having a recognised crypto currency. Can you also provide a sample csv record?

Hello
Thanks for your answer. I try to explain in english.
I’m trying to import a csv file for incoming distributions of my crypto account. I select “account transactions” and I see that there is an unassigned required field: value.
So I can’t click “next”!
But I do not have this field “value” since I receive CRO. The other fields are:
Transaction description for me is “card cashback” or “crypto earn”
Currency = currency
Amount = parts
Manually I do with “incoming distribution”, but it is restrictive and above all there is a risk of copying error.

Thanks in advance foryour help.
Regards.
Herewith the example file.
crypto_record_example.txt (502 Octets)

Supported currencies seem to be listed under General Data, Currencies. There is no CRO, presumably because it’s not in https://www.six-group.com/dam/download/financial-information/data-center/iso-currrency/lists/list_one.xls (see ISO 4217 - Wikipedia). #1633 mentions crypto support.

It is not a problem of crypto. It is more probably something I do not understand in the import of csv file. Maybe my question in not clear…
Thanks
Andre
PS: here a picture of my screen when I try to import…
csvin
I hope it can help to understand.

Amount should be associated with valeur, not parts.

I am not able to import transaction data from a CSV file even though all the fields appear to be properly mapped. Can any one help. I am running an iMac with MacOS High Sierra Version 10.13.16. As you can see on the attached file, the fields are green, I have tired chosing different options in the Type of Data but still I am not able to import (I do not get the Finish button to turn green).
CSV.pdf (56.5 KB)

Top of screen:

Unmapped required field(s): Value

Add transaction value.

Edit: Try Date, Type, Value where Type is “Fee”.

In addition to the comment from @flywire I suggest to export a demo transaction you want to import first. Then you have a template for the import.

That worked!!! :grinning: Thank you so much for your help.

1 Like

Is it possible to do this with some sort of Select All > Set provider to Yahoo > Update all?

I have imported 3 years of transactions, but the list is long. Lot of work to do it manually.

2 Likes

Firstly, if you think that would be a useful feature I’d encourage you to add a supporting emoji on the issue in GitHub.

When the data file is updated it will look like the following:

<client>
  <version>51</version>
  <baseCurrency>AUD</baseCurrency>
  <securities>
    <security>
      <uuid>831d8a3e-7025-4b0f-b6ca-3ef5bf727eb4</uuid>
      <name>REA Group Ltd</name>
      <currencyCode>AUD</currencyCode>
      <tickerSymbol>REA.AX</tickerSymbol>
      <feed>YAHOO</feed>
      <prices>

Try this and let us know how it goes.

This is totally not supported so backup your data file. Then open the data file with a text editor (eg np++ in Windows) before you add the providers.

It might be a search and replace of :

</tickerSymbol> 
     <prices>

with

</tickerSymbol>
      <feed>YAHOO</feed>
      <prices>

The image below shows the np++ Extended interface which uses a \n as new-line and the groups of 4 spaces are important:

image

2 Likes

Thanks. Solved. :+1:t2:

noone else than me interested in this?

Because you should post it to Issues · portfolio-performance/portfolio · GitHub