INDZARA

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.

  1. 8 commonly traded currencies are tracked for performance
    1. New currencies can be added easily
  2. 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).

Forex Rates in Excel

You can change the drop down to Change %, to see the rate Change %.

Choose Price or Change %

The tracker instantly updates to show the Change %.

Forex Change % for Currency pairs

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.

Refresh stocks data

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.

Add new currency rows

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

Convert to Stocks

Step 4

Verify that the stocks data is now populating in the next 3 columns. 

Stocks data populates automatically

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.

Related Templates

Exit mobile version