You can use this template to track the Forex Rates (Currency exchange rates) market.
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 and Free Excel for the web
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 add more currencies?
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.
The third column PAIR (currency pair) is calculated using a formula. Then, copy paste those 14 values and enter in the next column Ticker.
Convert the new 14 values in Ticker column to Stocks
Verify that the stocks data is now populating in the next 3 columns.
Unlock or Unprotect the sheet with indzara as password.
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.
Here is the link to the article from Microsoft which shows which exchanges are available and the delay in data refresh for each.
How to refresh?
To refresh the price and other information, just use the regular Excel refresh in the Data ribbon.
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.