Historical stock prices normalized in US$

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
Topic Author
siamond
Posts: 6008
Joined: Mon May 28, 2012 5:50 am

Historical stock prices normalized in US$

Post by siamond »

Does anybody know where to find a way to either download or graph the historical stock price of an individual security (or compare several securities), while normalizing the prices in US dollars?

All the various historical stock quotes/charts I know display a non-US (foreign) security with prices expressed in the local currency of the corresponding stock exchange... There used to be a hidden way to query Morningstar and make it do such a currency conversion/normalization, but not any more... Ideas welcome.
User avatar
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

Re: Historical stock prices normalized in US$

Post by Tyler9000 »

MSCI has a nice tool for downloading foreign index data in multiple currencies. Currency-specific returns for ETFs are indeed more difficult to come by, but if you can find the returns in local currency, translating them to USD isn't too hard.

What I do is download the historical exchange rates from the Federal Reserve and convert them to a percent change. It's the same as using CPI to calculate inflation. Then you can use that exchange percentage to translate the returns numbers to USD. In the percentage format, it's the same process as an inflation adjustment.

With the right spreadsheet skills, you might also be able to automate the process of collecting the exchange rates and doing the currency conversion. I know Excel has a new "Currencies" option that can look up exchange rates, and I imagine Google Sheets has something similar.

So worst case, you can create a spreadsheet with a Local returns column than can easily translate numbers to USD returns column. And best case, maybe you can find a way to scrape an internet source you like to automatically pre-fill that Local column with less effort. You'd think the Excel stock data would be good for that, but unfortunately it's pretty useless since for some reason it only offers a price option that ignores dividends.
User avatar
JoMoney
Posts: 16260
Joined: Tue Jul 23, 2013 5:31 am

Re: Historical stock prices normalized in US$

Post by JoMoney »

Can you provide an example stock you want "normalized" in US$ ?
I'm not sure I understand what you mean by "normalized" you just want the historical price quotes charted but using US$ as the base currency, or something else?
"To achieve satisfactory investment results is easier than most people realize; to achieve superior results is harder than it looks." - Benjamin Graham
hi_there
Posts: 1182
Joined: Sat Aug 29, 2020 7:00 pm

Re: Historical stock prices normalized in US$

Post by hi_there »

So, if I understand your question, you are looking at a foreign stock - let's say denominated in EUR. You want a historical time series of the stock's value, translated to USD at the relevant historical FX rate.

Sounds like you already have access to historical price data in the local currency of the stock. If so, it should be a simple matter to find free FX data, for instance from the link below or similar, and perform the currency translation in Excel.

Unless I misunderstood something...

https://excelrates.com/
User avatar
Topic Author
siamond
Posts: 6008
Joined: Mon May 28, 2012 5:50 am

Re: Historical stock prices normalized in US$

Post by siamond »

JoMoney wrote: Wed Jun 23, 2021 10:56 pm I'm not sure I understand what you mean by "normalized" you just want the historical price quotes charted but using US$ as the base currency, or something else?
Yes, historical price series using US$ as base currency. For individual securities (i.e. public companies), US-based or foreign.
hi_there wrote: Wed Jun 23, 2021 11:10 pm So, if I understand your question, you are looking at a foreign stock - let's say denominated in EUR. You want a historical time series of the stock's value, translated to USD at the relevant historical FX rate.
Yes, this is indeed what I am seeking, but performed in a SINGLE step (historical price query + translation in US$).
User avatar
Topic Author
siamond
Posts: 6008
Joined: Mon May 28, 2012 5:50 am

Re: Historical stock prices normalized in US$

Post by siamond »

Tyler9000 wrote: Wed Jun 23, 2021 10:35 pmWith the right spreadsheet skills, you might also be able to automate the process of collecting the exchange rates and doing the currency conversion. I know Excel has a new "Currencies" option that can look up exchange rates, and I imagine Google Sheets has something similar.

So worst case, you can create a spreadsheet with a Local returns column than can easily translate numbers to USD returns column. [...]
This is indeed a possible backup option. Triggered by your post, I found out that Google Sheets has the following function to get an exchange rate from USD to GBP (or other combinations of currency symbols):
=GOOGLEFINANCE("Currency:USDGBP", "price", DATE(YYYY,MM,DD))

I am always a little wary about GoogleFinance() functionality, which didn't prove terribly reliable in my past experience, but maybe this specific exchange rate processing does work well...
Post Reply