Excel trick uses a single cell to get the latest stock or exchange rate

Office-Watch.comLast updated: 15 November 2023

Microsoft 365,Microsoft Excel,Microsoft Office,Office 365

Here’s the 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 and the annoying lack of configuration in the standard stock/currency data type.

The single cell trick makes for more compact and workbook plus letting you select from different currency combinations using variables. We’ll explain how it works and the downsides. In case the single cell method isn’t enough, also we’ll explain the standard method too.

StockHistory()

We’ll make use of the StockHistory() function in a tricky way 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)

Real Time Excel – stock prices and more
Put the latest stock prices, indexes, exchange rates even weather details automatically into Excel, Word and PowerPoint.

Available NOW for Excel 365 Windows/Mac and earlier versions of Office.

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 for that day 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 or even Today()-2 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.

Using StockHistory() to get automatic currency choices

A real annoyance with Currency data types is that you can’t use a formula to create a Currency data type. Each pair e.g. USD/CAD, GBP/EUR, AUD/USD has to be typed into a cell then select Data | Data Types | Currencies. There’s no way to use the data type to choose two currency labels, say from a drop down list then get that Currency data.

The workaround is to use StockHistory() with a text joining formula for the first parameter.

=STOCKHISTORY(C2&"/"&D2,TODAY()-2,,0,0,1)

Where C2 and D2 are two currency codes (e.g. USD, GBP, EUR etc) separated by a “/”.

Now you can change the two cells (C2 and D2) and Stockhistory() will show a near-ish exchange rate.

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