Hi I am a UK user trying to set up my portfolios in Portfolio Manager. I have a lot of historical data. I have successfully downloaded the data from a csv. Initially I set up the portfolio in GBP. Unfortunately Yahoo provides quotes in GBX, mostly. I retried the portfolio with a default of GBX but it reverts to GBP. Is there a way of using the currency conversion to assist with this. When I try and set a currency for an asset it says the security has transactions recorded and the currency cannot be changed any more. Any ideas?
You should put your main accounts in the currency thatâs best for you (probably GBP)
Then I recommend to set up securities in their âmainâ currency (GBX, USD, w/e), and making sure to get the quotes from yahoo finance (or any other providers) in the same currency
You should have no issue in creating tx afterwards for these securities, even if the currency does not match your main currency, youâll have the conversion happening & displayed in the buy / sell popup
cf attachment with an example on my side (my main cash / security account are in âŹ, and this is an example with a stock in PLN)
1 Like
Thanks for the prompt repose. I am new to the software so still understanding the file structure. I think the problem is I am importing legacy transactions and the quotes are in GBX but the value is GBP. So the current valuations are wrong. I canât amend the currency for the downloaded securities and a new buy for an existing security does not show a currency option. I assume these could be set for a new security. I did wonder whether I could use a JSON download from yahoo which you can put in a factor to change the quote but I am not familiar with JSON and what URL might work.
Finally figured it out. Create the account in GBP. Create the security in GBX/GBP depending on the yahoo quote before importing the historical transactions. Then for GBX quotes create a gross currency amount in the transaction spread sheet by multiplying the value by 100 and an exchange rate of 0.001. Phew!!
Facing the same problem and trying to use your solution (thanks for posting this as a follow up). I created the account in GBP and then edited all the securities (before importing anything) to GBX or GBP depending on what Yahoo provides for that security. I have then changed my import CSV to match these GBP/GBX settings and converted values to these âcurrenciesâ where needed. When I try to import however, PP throws errors for all the GBX securities saying that the currency does not match the account currency. It crosses them out and wonât import. I havenât done the exchange rate step you mention and Iâm guessing thatâs the key. Could you expand on that please? I found the currencies section and can see there is an existing GBX/GBP exchange built in. Iâm not sure what I need to create or where to get this to work. Thanks.
1 Like
From memory. For all my active securities I created the security in the portfolio before importing any data. So those with quotes in GBX create the security as GBX, The portfolio stays in GBP. For the import I created extra columns in the spread sheet `to create a currency gross amount (value times 100) in GBX, a buy price (quote) in GBX and a currency conversion factor of 100. This works on the import only on either account transactions or portfolio transactions, I canât remember which. I also found the data could be rejected if the spreadsheet wasnât clean i.e. only the rows and columns you need to import.
Separately I struggled to import dividends so the earnings and cash position were difficult to capture. In the end I took a total earnings and Imported it as one figure. It is not totally clean but it gives a better picture of performance.
It has been a few weeks since I did this but I have more to do so if it doesnât work I will be doing some more data sets this week and will capture the process.
1 Like
Thanks again. I seem to have followed all these steps apart from this which I havenât a clue where or how to doâŚ
I tried adding a Gross Amount in GBX to the import CSV and then called this âCurrency Gross Amountâ for import, but it made no difference. All the GBX transactions were flagged as not being in GBP and wonât import.
I donât know what a âportfolio transactionsâ are so donât know how to try the import there (in case thatâs what worked for you).
If you could drop some hints when youâve next had a go, that would be much appreciated as Iâm stuck after a huge amount of work assembling the data to import. It seems like a miss in the tool that it canât just handle (or be told that) the quote is in GBX .
Iâll keep trying in the meantime in case I can stumble across the answer.
OK - I see where to select Portfolio vs Account transactions (although I donât know what this means yet). Made no difference.
I tried importing the transactions with currency set to GBP in the CSV file (security is defined as GBX) and created an Exchange Rate column with 100 for the GBX securities. The error is now different âExchange rate of gross value is missingâ. Doesnât appear to be another column name I could add for this.
Somewhat confused again by the terminology since Gross Amount and Value are different things. âGross Valueâ?
Hi Appologies I should have said an exchange rate of 100, it is one of the import mapping parameters. I was watching the rugby when I wrote.
So for GBX quotes I set up the security as GBX and then inported the fields; Security Name,Ticker Symbol,Type,Date (make sure you use the right format),Shares,Fees(GBP), Taxes (GBP), Value(GBP),Currency Gross Amount(Add to the CSV file a new column, Value * 100),Exchange Rate(100, also new column).
I found the transaction drop down menus gave a good idea how the program processes the data.
For shares/funds I have bought and sold I just imported them as GBP value as I no longer track them.
When you import a csv file the top drop down menu gives you some options the first being account transactions, the second being portfolio transactions. One of them worked for me.
If it helps. I started importing all my historical data. I couldnât import dividends easily and the record of buying creates a cash deficit. I found it impossible to get a clean historical record.
I have a lot of data and it was getting very messy.
I decided the cleanest solution was to start with the current position. So I input a delivery inbound (not a buy) for the current holdings using the average buy price (I have a lot of dividend reinvestment) and a one off dividend of the total of the historical earnings.
I am transferring from stockmarketeye and the prices page make this fairly easy to do.
You lose the history it is not perfect but at least retain a fairly good idea of the historical share/fund performance.
Regards
1 Like
Thank-you. Got there!
So my import CSV has Security Name, Type, Shares, Date, Price (ignored on import), Fees (I havenât got taxes separately), Ticker Symbol (which is the Yahoo! symbol in my case), Value (GBP), Gross Amount (GBP) (which is Value plus fees), Currency Gross Amount (Gross Amount x 100), Exchange Rate (value of 100 for all the GBX securities - I have two/three which Yahoo! prices in GBP so the Exchange Rate is 1 for those).
The changes which I needed to make to get this to import were: delete a column I had for Transaction Currency (which said GBP) and change âType of Data fieldâ on the import dialogue to âPortfolio Transactionsâ from âAccount Transactionsâ (the latter will not work as you mentioned).
Overall positions seem to make sense although as you say, now I need to figure out what to do about the cash inputs, dividends etc. Will try your suggestions.
I need to figure out Account vs Portfolio. At the moment I have just imported one account for one of us (we have three each - ISA, General and SIPP) so will need to figure out whether this works when trying to be able to see those separately and in aggregate. Worst case I just lump them all together.
BUT - big progress, so thank-you very much for being so helpful.
Stuart
2 Likes
As you also mentioned, date format caught me out, but I was able to reimport and choose the date format by double clicking on teh date column (i.e. no need to change the import CSV).
For anyone reading this thread, there is now a How To:
Stuart