How to Get Historical Exchange Rates for a Specific Time in Excel
Question details
The user wants to retrieve foreign exchange rates for an exact date and time (intraday timestamp) to calculate currency conversions for recorded trades in Excel.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating USD values from AUD amounts based on trade logs that require exact intraday timestamps (e.g., 9:00 p.m. on August 15, 2021).
- Observed behavior
- Excel's native Stocks data type and STOCKHISTORY function return current or daily historical rates but lack the capability to pull foreign exchange prices for an exact intraday timestamp.
Ensure you have an active internet connection to query financial data, and verify that your trade records contain standardized date and time formats before importing external exchange rates.
Use an External Financial API via Power Query
Since Excel does not natively support exact intraday exchange rates, the best approach is to connect to a third-party financial data provider that offers timestamped forex data.
Excel's native STOCKHISTORY function is strictly limited to daily data points (Open, High, Low, Close). To match a currency pair rate with an exact time like 9:00 p.m., you need to fetch the data from a dedicated financial API (such as Alpha Vantage, Fixer.io, or Open Exchange Rates) and import it into Excel.
Register for an account with a financial data provider that offers historical intraday foreign exchange rates and obtain your unique API key.
In Excel, navigate to the Data tab on the ribbon, click on 'Get Data', select 'From Other Sources', and choose 'From Web'.
Paste the API URL provided by your service into the URL box, making sure to include parameters for your required currency pair (e.g., AUD/USD), date, time, and API key.
Once the data loads into the Power Query Editor, drill down into the JSON or CSV records to isolate the exact exchange rate for your specified timestamp.
Click 'Close & Load' to bring the rate table into your worksheet. Use VLOOKUP or XLOOKUP to match the imported rates with the specific timestamps in your trade log.
Use Excel Stocks for Daily Closing Rates
If exact timestamp precision is not strictly required, use Excel's native Stocks data type as a quick alternative to pull daily historical rates.
Need a Fast, Reliable Spreadsheet Tool? Try WPS Office
While fetching highly specific API data is required for intraday exchange rates regardless of the spreadsheet software, managing large volumes of imported financial data requires a stable and lightweight application. WPS Office is a powerful alternative that handles massive datasets with ease while maintaining full compatibility with your existing workbooks.
- 1. Download WPS Office: Visit the official WPS website to download the free installation package for your operating system.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx trade logs directly without any format conversion.
- 3. Analyze Data: Continue using standard lookup and math formulas to calculate your USD to AUD conversions seamlessly.
Frequently Asked Questions
Does the STOCKHISTORY function support hourly or minute-by-minute data?
No, the STOCKHISTORY function natively supports only daily, weekly, or monthly intervals. It can provide the Open, High, Low, and Close rates for a specific day but cannot fetch the rate at an exact time like 9:00 p.m.
How do I format currency pairs to get exchange rates in Excel?
Enter the currency pair using standard ISO currency codes separated by a slash or colon, such as 'AUD/USD' or 'EUR:GBP'. Once entered, select the cell and apply the 'Stocks' data type from the Data tab to enable financial fields.
Are there free tools to get exact historical exchange rates into a spreadsheet?
Yes, many financial API providers (like Alpha Vantage or ExchangeRate-API) offer free tiers that allow a limited number of daily queries. You can connect these APIs to your spreadsheet using the 'Get Data from Web' (Power Query) feature to retrieve specific historical timestamps.
