Currency percentages

🤝 Help
1

Is there a way of simply seeing what percentage of my holdings are held in certain currencies?

i.e. 30% in USD 70% in GBP?

1 Like
2

Does this already exist in the app or is there a way of working this figure out?

3

@iAndy, sorry for the delayed reply. There isn’t such an option, but I understand how it would provide valuable insights about the portfolio, and I’ll pass your feedback on for consideration.

1 Like
4

There is a way to do it semi-automatically with a reasonably recent copy of Excel. Older versions can work, too, but you’ll need to put more effort into it.

  1. Export your order history (uncheck transactions, dividends etc, include only orders) — all of it, if feasible. There are at least two place you can do this from, the easiest to access is the History button right at the very bottom of the pie page, then the page with down-arrow at the top right.
  2. Open a new Excel workbook
  3. Data→From Text/CSV, point to exported CSV and click ‘Load’ — this should give you an Excel table where just double clicking on the CSV file won’t
  4. Table Design→Table Name, change the table name to T
  5. The import process added a sheet. Go to the original Sheet1 and enter the formulae as listed below. Yes, they’re ugly, sorry, but the final result should look something like this:

image
image781×355 13.6 KB

  1. In A2: =SORT(UNIQUE(T[Ticker]))
  2. In B2: =SUBSTITUTE(IF((SUM(FILTER(T[No. of shares],ISNUMBER(SEARCH("buy",T[Action]))*(T[Ticker]=A2)),0)-SUM(FILTER(T[No. of shares],ISNUMBER(SEARCH("sell",T[Action]))*(T[Ticker]=A2),0)))>0.00000001,XLOOKUP(A2,T[Ticker],T[Currency (Price / share)]),""),"GBX","GBP")
  3. Drag the fill handle (little square in lower right corner, cursor turns to a +) down to the row with the last ticker showed in Column A
  4. In D1: =SUBSTITUTE(SORT(UNIQUE(B:B)),0,"")
  5. In E2: =COUNTIF(B:B,D2) — No, the 2 is not a typo, D1 contains an empty cell that is an artefact of UNIQUE()
  6. Fill E2 down, as required
  7. In the cell a row or two after that, key sequence Alt+= to sum the column and note the row number you’re in
  8. In F2: =E2/E$6 — substitute the 6 for the row you just noted
  9. Fill F2 down, as required
  10. Do any formatting niceties you’d like, eg format column F as percent, increase decimal places, add labels etc.

NB: this result isn’t weighted by value. You can adapt the formula to do that, if that’s what you want. Ping me if you need help.

How it works:

It relies on spill formulae and Excel table intersection references which old versions of Excel might not support. SUBSTITUTE() is used to map GBX to GBP and to get rid of the errant 0 that you get from including empty cells. The rest of it should be fairly self-explanatory except for the big one in B2. Here’s it broken down:

  1. =IF(..., XLOOKUP(A2,T[Ticker],T[Currency (Price / share)]),"") fetches the currency symbol associated with each ticker, or returns nothing for symbols no longer held, where ... is
  2. SUM(FILTER(T[No. of shares],ISNUMBER(SEARCH("buy",T[Action]))*(T[Ticker]=A2)),0) counts the number of shares bought for each symbol named in the cell to the left
  3. SUM(FILTER(T[No. of shares],ISNUMBER(SEARCH("sell",T[Action]))*(T[Ticker]=A2),0) counts the number of shares sold for each symbol named in the cell to the left
  4. IF( shares_bought - shares_sold > 0.00000001, symbol_currency, "" ) returns the symbol currency only if you still hold a position. The reason for the > 0.00000001 rather than comparing with 0 has to do with floating point rounding errors and how very, very small residual differences can sometimes be evaluated as #TRUE rather than #FALSE.
2 Likes
5

Thanks for this. I will give it a go.

1 Like