Excel single cell trick to get the latest stock or currency rate
Here’s an Excel trick to grab a recent stock price or currency exchange rate using a single cell. It bypasses the multiple steps needed for the Stock/Currency data type then linking that info to other places in a workbook.
The single cell trick makes for a more compact and easy to understand workbook. We’ll explain how it works and the downsides. In case the single cell method isn’t enough, we’ll explain the standard method too.
We’ll make use of the StockHistory() function to get a single price instead of the usual table of results. It looks like this for a stock price or currency rate.
As you can see, we’re using StockHistory in an unconventional way. Instead of getting a series of results with dates and prices, we’ve tweaked the StockHistory() parameters to a just one date and a single result.
It’s an undocumented way to get an exchange rate or price, and only the rate/price. The parameters look like this:
- The stock ticker code or currency rate
- Today() or Today()-1 to get today’s date … more on this below.
- End Date – leave blank, StockHistory() defaults to the start date if this is blank.
- Interval – leave blank or use 0 (zero)
- Headers – 0 (zero) to stop headers appearing
- Properties or what data to return – use 1 to get Closing price
If you just make the sixth parameter Close (1), Excel will return just that value – no headers, or dates like this:
=STOCKHISTORY("MSFT",TODAY()-1,,0,0,1)
The Today() trap
Did you notice we used Today()-1 (yesterday) not just Today() – why?
Using Today() to get the current rate might not work because the markets haven’t closed or opened depending on your time zone.
The currency rates appear to come from US East Coast, as near as we can tell. If you’re east of that (UK, Europe, India, Australia etc) then Today() might return the day after not the current US day.
For example, it’s Wednesday morning in Europe but still Tuesday afternoon in New York. In Europe Today() gives Wednesday’s date so StockHistory() asks for the closing price on Wednesday, which doesn’t exist yet because in New York it’s still Tuesday.
Even in the USA, Today() and asking for the Closing price won’t work until the early evening when there is a closing price for the day.
You’re safer to use Today()-1 to ask for the closing price of the previous day, otherwise you might see the return #VALUE! as shown below.
In extreme cases (just east of the International Date Line, Australia, NZ, East Asia), you might need to use Today()-2 .
Limitations
Of course, you’re not getting the very latest price, that’s what the Stock and Currency data types are for.
We use the StockHistory() method mostly for currency exchange rates where the very latest price isn’t important. For example, in a budget or expenses calculation that has a few values in other currencies which need conversion to your main currency.
Get the very latest stock or currency rate
If you want the latest * stock quote or currency exchange rate, here’s the full and official way to do it.
In short:
- make a table of rates using Excel 365’s Stock or Currency data types.
- Link to those prices from anywhere else in your workbook
- Optional – give each price cell a name for easier readability e.g StockPrice_Alphabet or Xrate_USDtoAUD
* we say ‘latest’ because Excel 365 Stock and Currency data type results are NOT real time.
Excel’s Stock / Currency data (kinda, sorta) gets auto-refresh
Put the latest Stock and Fund prices in Excel workbooks
Degree Celsius ℃ symbol in Word, Excel, PowerPoint and Outlook
Four ways to split date into day month year in Excel