Forex Rate Tracker in Excel – Live Foreign Exchange data
You can use this template to track the Forex Rates (Currency exchange rates) market.
Requirements
Microsoft has introduced a new feature in Excel called Data types. One of the data types is ‘Stocks’. That’s what we will be using for these templates. This feature is available in Microsoft 365 Subscription Plans for PC and Web
For all the templates in this ‘Excel for Stock Market’ series, please visit indzara.com/stock-market-templates
Download
Video Demo
How to use the Forex Market Tracker Template?
This template can be used to view the Forex rates for the most commonly traded currencies.
- 8 commonly traded currencies are tracked for performance
- New currencies can be added easily
- Toggle between Rate and Change %
The template shows the currency exchange rates for the 8 currencies (EUR, USD, AUD, GBP, NXD, CAD, CHF and JPY).
You can change the drop down to Change %, to see the rate Change %.
The tracker instantly updates to show the Change %.
The positive changes are in green and the negative changes are in orange.
On the top right you can see the maximum of the last trade time of these currencies.
The default time that Microsoft provides is UTC. The offset is set to -7 hrs, to convert to pacific time zone.
You can modify this if you need the time in a different time zone. Visit https://savvytime.com/converter/utc to view offset
How to refresh?
To refresh the price and other information, just use the regular Excel refresh in the Data ribbon.
How to add more currencies?
Step 1
In the Forex Data sheet, add new currency by entering all the corresponding pairs in the first two columns. For example, if I need to add INR as a currency, I have to enter 7 currency pairs from INR and 7 to INR.
Step 2
The third column PAIR (currency pair) is calculated using a formula. Then, copy paste those 14 values and enter in the next column Ticker.
Step 3
Convert the new 14 values in Ticker column to Stocks
Step 4
Verify that the stocks data is now populating in the next 3 columns.
Step 5
Unlock or Unprotect the sheet with indzara as password.
Step 6
Update Currency code and Flag in the Forex Rates Tracker sheet.
That is it. The new currency will be automatically updated now.
Lock the sheet with password to protect the formulas.
The foundation of this template is the Stocks Data type and StockHistory function.
Please read more on Stocks Data Type.
Please read more on StockHistory function and how we use to extract the price history of a stock or currency.
Here is the link to the article from Microsoft which shows which exchanges are available and the delay in data refresh for each.
Feedback
Please share your experience. I expect this to be different with different exchanges and countries. Is it working for you? Is it working differently? I would love to hear from you.
4 Comments
Hi, I hv excel 2007, and this template does not work , it does not refresh,
do i need the other data sheet also, where to get it
rgds
Raj
Thank you for showing interest in our template.
This template requires Excel 365 subscription to use Excel Stock datatype feature as stated in our requirement on the template page.
Best wishes.
hello
A great video and I have subscribed.
I have downloaded Forex Rate Tracker Excel Template but it is only read only. What is the password please?
Also the Stock History Function link is broken. I would like to understand this more. Can
Thank you for showing interest in our templates.
The password to unprotect the sheet is indzara. Requesting to share your sheet to support@indzara.com to check further on Shock History Function link. Also requesting to confirm your Excel version in the same.
Best wishes.