In this post I show you how I convert the value of my stocks to local currency. This is DKK for me, but it could be EUR, USD, INR, GBP or some other currency for you. Below I have made a fully functional spreadsheet demo that can calculate what your portfolio is worth in your own currency. If you want a walkthrough, keep reading as we will start with some basics.
You can find a working demo of this post HERE. You just have to change the base currency to see it in your currency of choice.
Disclaimer: The stocks in this post have been picked randomly. I currently own none of the stocks in this post (as of 2022-04-11), this post is not meant to be for or against buying any of the mentioned stocks.
How to get the price of a stock
I have dedicated a whole post on how to get the price of a stock. As a simple example you can get the current price of amazon using the following:
=GOOGLEFINANCE("NASDAQ:AMZN")
Which gives you the price of 3089,21 as of today:
If your local currency is USD, you are now done, but your needs are likely beyond this simple example. Note that in the example above we use the exchange (NASDAQ) as a prefix. When fetching prices from Google finance there might be an overlap in the ticker of stock as they are not unique across exchanges. You can therefore prefix it with the exchange to guarantee uniqueness.
Getting the price in your local currency
In a previous post I made an example on how to get the exchange rate for every currency against a base currency. You can use this for getting the exchange rate for the currencies that you have stocks in. It creates a table like the following:
For example the value of the cell B19 is the following formula:
=IF(A19 = $B$16, 1, GOOGLEFINANCE(CONCATENATE("CURRENCY:",A19,$B$16)))
In the above we call the Google finance function to get the exchange rate for a currency. We concatenate the currency in A19 together with the base currency in B16 to get the exchange rate, for example CURRENCY:USDEUR. The IF where we default to 1 is made because the function fails if the two currencies are the same, like "CURRENCY:EUREUR" (luckily the exchange rate between two of the same currency is 1).
If your base currency is Euros you can easily calculate the price of Amazon using this:
=GOOGLEFINANCE("NASDAQ:AMZN")*B25
Which will give you 2.830 as the exchange rate between EUR and USD is 0,92 as of this writing.
Putting it all together
Using the table for getting the exchange rates we can create the following table:
The link to the above table is HERE.
In the above I have picked some stocks from different exchanges in different currencies. I have added some random quantities (ok, not entirely random I tried to hit around 2.500 EUR each). The next column is the price fetched from Google finance, basically =GOOGLEFINANCE("<Bx>"). I then define what the original currency of the stock is in column E, which I provide on my own, as I have not found a good way of finding this yet. In column F we fetch the exchange rate using a lookup based on the Currency (Ex) and the exchange table we created previously:
=LOOKUP(E2, $A$19:$A$25, $B$19:$B$25)
The above is for fetching the exchange rate for Amazon (E column) from the exchange table. We can then calculate the value in your local currency by applying the exchange rate to the original price:
=D2*F2
In the end we can add the quantity you have in stock and calculate the total in local currency:
=C2*G2
This gives 2.831,76 currently for Amazon in EUR. At the end you can sum it all up to get a total in your local currency:
=SUM(H2:H10)
That is it
I hope you found this helpful, please leave a comment down below. You may also share what you have done if you have found a smarter way to do this!