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.
Historical stock prices normalized in US$
Re: Historical stock prices normalized in US$
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.
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.
Re: Historical stock prices normalized in US$
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?
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
Re: Historical stock prices normalized in US$
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/
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/
Re: Historical stock prices normalized in US$
Yes, historical price series using US$ as base currency. For individual securities (i.e. public companies), US-based or foreign.
Yes, this is indeed what I am seeking, but performed in a SINGLE step (historical price query + translation in US$).
Re: Historical stock prices normalized in US$
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):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. [...]
=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...