The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →The quickest way to retrieve a historical currency rate in Microsoft 365 Excel is STOCKHISTORY. For an official, refreshable reference series, import the European Central Bank (ECB) data with Power Query. Choose based on what your transaction, payroll, tax, or accounting records require: a provider-supplied market observation or a named central-bank reference rate.
First, define which “exchange rate” you need
A date can have several valid rates. Excel’s financial-data service may provide a daily closing or other market observation, while a central bank publishes a reference rate. A bank, card network, broker, payment processor, or accounting system may apply its own timestamp, spread, markup, or legally specified convention.
Therefore, record the source, pair direction, date, and rate type with your calculation. A Microsoft financial-data value is not automatically the rate required for tax, customs, payroll, or financial reporting.
Method 1: Use STOCKHISTORY for a quick historical rate
STOCKHISTORY is available in Excel for Microsoft 365 and Excel for Microsoft 365 for Mac with an eligible subscription. It retrieves service-backed financial data and returns a dynamic array.
Recommended Free Tools
#1 Best Overall
Get one day’s rate
Enter this formula in an empty area of the worksheet:
=STOCKHISTORY("USD/GBP",DATE(2025,6,30),DATE(2025,6,30),0,0,0,1)The result contains the requested (or available) date and the closing value for the USD/GBP pair. In this notation, USD/GBP means the number of British pounds for 1 U.S. dollar.
Return only the numeric rate
Use INDEX when a single number is needed for another formula:
=INDEX(STOCKHISTORY("USD/GBP",DATE(2025,6,30),DATE(2025,6,30),0,0,1),1,1)Using DATE(year,month,day) avoids regional ambiguity in text dates such as 6/30/2025.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Drive the formula from cells
Put the pair in B1 (for example, USD/GBP) and the Excel date in A2:
=INDEX(STOCKHISTORY($B$1,$A2,$A2,0,0,1),1,1)Copy the formula down for a transaction list. Use ISO-style three-letter codes such as USD, EUR, GBP, JPY, CAD, AUD, or CHF; do not use an ambiguous symbol such as $.
Retrieve a range of dates
To return daily observations from June 1 through June 30, 2025:
=STOCKHISTORY("USD/GBP",DATE(2025,6,1),DATE(2025,6,30),0,1,0,1)The output spills into two columns: date and close. If your Excel version supports DROP and you need only the rate column:
=DROP(STOCKHISTORY("USD/GBP",DATE(2025,6,1),DATE(2025,6,30),0,0,0,1),,1)Leave the spill area empty. Occupied cells can cause a #SPILL! error.
Understand the arguments
| Argument | Meaning |
|---|---|
stock |
Ticker, currency pair, or other supported financial instrument |
start_date |
First requested date |
end_date |
Last requested date |
interval |
0 daily, 1 weekly, 2 monthly |
headers |
Controls returned headers |
0 |
Date property |
1 |
Close property |
2 |
Open property |
3 |
High property |
4 |
Low property |
5 |
Volume property |
Full syntax is:
=STOCKHISTORY(stock,start_date,[end_date],[interval],[headers],[property0],[property1],[property2],[property3],[property4],[property5])Microsoft describes the function and its Microsoft 365 availability at the STOCKHISTORY documentation. Historical values generally update after a trading day completes, so this is not an intraday or guaranteed real-time tool.
Method 2: Import an official ECB reference-rate table with Power Query
Use this method when you need a documented central-bank reference series and a refreshable table. The ECB data is euro-based, so pairs that do not include EUR require a cross-rate calculation.
Connect Excel to the ECB CSV endpoint
For a broad daily dataset, use:
https://data-api.ecb.europa.eu/service/data/EXR/D..EUR.SP00.A?format=csvdataFor a narrower USD/EUR period, use:
https://data-api.ecb.europa.eu/service/data/EXR/D.USD.EUR.SP00.A?startPeriod=2025-06-01&endPeriod=2025-06-30&format=csvdata- Open a workbook and select Data > From Web.
- Paste the ECB URL and choose Basic if prompted.
- Select Load, or choose Transform Data to clean the preview.
- Set the observation date to the Date type and the rate to Decimal Number.
- Rename fields to practical names such as
Date,Currency, andRate. - Select Close & Load.
- Use Data > Refresh All whenever the source should be updated.
Excel’s Web connector and the ECB’s refreshable-workbook guidance are documented at Microsoft’s From Web instructions and the ECB refreshable Excel guide.
Rank #4
- Used Book in Good Condition
Look up a rate in the imported table
If the loaded table is named ECB_Rates and already represents the required pair:
=XLOOKUP(A2,ECB_Rates[Date],ECB_Rates[Rate],"",0)The final 0 requests an exact date match.
Calculate a non-euro cross-rate
Label values explicitly, for example EUR_per_USD and EUR_per_GBP. Then calculate:
=EUR_per_USD/EUR_per_GBPIf the ECB table shows 0.92 EUR per USD and 1.17 EUR per GBP, the USD-to-GBP rate is approximately 0.92/1.17 = 0.7863 GBP per USD. The ECB API’s daily frequency, date filters, and CSV parameters are described at the ECB API examples and the ECB data API reference.
Use the rate to convert an amount
Suppose A2 contains the date, B2 contains an amount in USD, and C2 contains the USD/GBP rate. Convert USD to GBP with:
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitches=B2*C2You can calculate the rate inline:
=B2*INDEX(STOCKHISTORY("USD/GBP",A2,A2,0,0,1),1,1)For the reverse pair, invert the rate. If USD/GBP is 0.7863, then GBP/USD is:
=1/0.7863To convert an amount using a rate quoted as target currency per source currency, multiply. If your rate is quoted in the opposite direction, divide instead. Always verify the pair labels before applying either operation.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Handle weekends, holidays, and missing observations
Not every calendar date has a published observation. Weekends, public holidays, market calendars, and unavailable provider records can leave gaps. If your policy permits using the most recent prior rate, import a range and use an approximate-date lookup.
For an ascending table named Rates:
=XLOOKUP(A2,Rates[Date],Rates[Rate],"",-1)The -1 search mode returns an exact match or the next smaller date. Document that fallback; do not silently substitute a prior business-day rate when a tax, payroll, or accounting rule requires a different convention.
Why your Excel result may differ from a bank or accounting system
- Source: Microsoft’s provider, the ECB, and your bank may publish different series.
- Timing: A daily close, central-bank fixing, timestamped snapshot, and transaction-time quote are not interchangeable.
- Spread and fees: Card issuers and payment processors can add markups or charges.
- Pair convention: A reversed quote changes the numerical value.
- Date treatment: One system may use the prior business day for a holiday while another reports no value.
For auditability, preserve the source name, exact URL or query, retrieval date, pair orientation, date-fallback rule, and whether the value is a close, reference rate, or transaction rate.
Troubleshooting common errors
| Symptom | What to check |
|---|---|
#NAME? |
Confirm that you are using Microsoft 365 Excel with an eligible subscription; older perpetual versions may not include STOCKHISTORY. |
#VALUE! or no result |
Check the three-letter codes, slash pair format, date cells, internet connection, and whether data exists for the requested date. |
#SPILL! |
Clear cells blocking the dynamic-array output or move the formula to an empty range. |
| Question-mark icon on a currency | Excel could not match the pair to its provider. Try a valid ISO pair such as USD/EUR, reverse the pair, or use the ECB import. |
| No exact date in the table | Request a wider range and use the documented prior-observation lookup if appropriate. |
| Stale external data | Check the connection and select Data > Refresh All. |
The Currency data type is a separate Microsoft 365 feature: enter a pair such as USD/EUR, select the cells, choose Data > Currencies, and add fields such as Price or Last Trade Time. It is useful for current or refreshable pair information, not the primary historical-by-date lookup. See Microsoft’s currency exchange-rate guidance.
Which method should you choose?
| Need | Best choice | Reason |
|---|---|---|
| One historical value quickly | STOCKHISTORY |
Minimal setup and a direct formula |
| Many dates in one worksheet | STOCKHISTORY date range |
Spills a date/rate table |
| Official central-bank reference series | ECB Power Query | Named source with refreshable imported data |
| Older desktop Excel | CSV/API import or local rate table | Avoids Microsoft 365-only functions |
| Exact settlement amount | Bank, card, processor, or accounting source | Matches the rate actually applied to the transaction |
For general spreadsheet analysis, start with STOCKHISTORY. For a repeatable report or an official reference-rate requirement, use the ECB query and retain the imported snapshot or refresh documentation.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Free tools Windows power users keep installed
One-click scans. No signup required.
