Excel Forex Tester

Excel Forex Tester simulates trading in the Forex market using historical data. It allows to easily place market or pending orders, setup targets, and calculate profits. Additionally, this Forex Tester in Excel comes with a number of technical indicators, and can potentially be leveraged to plot customized indicators.

Forex Trading

The foreign exchange (Forex or FX) market is the most traded market in the world with a turnover of approximately $5 trillion per day. It opens 24 hours 5 days a week, and currencies trade over the counter in whatever market is open. Forex trading involves buying a particular currency and then selling another, profiting from currency fluctuations and speculation on future movements.

Forex backtesting allows to simulate trading using historical data. It is used to learn to trade or to test/improve trading strategies. There are several professional platforms available to do backtesting, but they usually have a subscription fee. Excel Forex Tester is an Excel-based application that resembles those platforms, and counts with the benefits of spreadsheets to manage your results of Forex trading in Excel. Additionally, it is free code and can easily be leveraged and customized with some basic Excel VBA knowledge. However, there are some obvious limitations compared to dedicated software (hey, Excel is just a spreadsheet software after all). If you are planning to take this a little more serious, then you should probably try some of the professional Forex backtesting software (some offer a free trial). In that case, I would strongly recommend Forex Tester, which is one of the leading Forex simulators in the market, and has been a reference and inspiration to develop this version of Forex Tester in Excel. Go ahead and try it for free. You can also see the main differences between Excel Forex Tester and Forex Tester in this other page.

Forex Tester - Get your free trial!

 

 

Data Set

In order to conduct a backtest in Excel Forex Tester, we need to create a new project following the command button on the main pane on the right-hand side.

When creating a new project we need to get a data set (or use what’s there by default), select the starting date and time, select the lot size, and make a deposit (fictionally set what’s the starting balance).

By default, Excel Forex Tester comes with historical daily prices in the EUR/USD market for the last 13 years (exactly from 01-Jan-2007 till 31-Dec-2020). We can easily import a different data set but need to make sure the source data is properly formatted. It must be an Excel file with date and time in the first column, and Open-High-Low-Close price values in columns B through E of the active sheet (see image below). Note there are not headings. The file name starts with the currency pair (for example, EURUSD1H.xlsx). Additional data sets for a number of currency pairs and time intervals can be found here.

Excel Forex Tester’s project settings dialog shows the currency pair, time interval (M1 for minutes, H1 for hours, and D1 daily), and start/finish dates for a given data set. If the currency pair is not recognized in the imported file, it will pop up a dialog to select the market.

Then, we can choose the exact date and time to start the test, which should span far before the end date of the historical data set. The deposit currency has to match either of the traded currencies in the pair chosen to perform Forex backtesting in Excel. The USD is set by default for pairs including US dollar. Finally, the lot size determines the number of trading units. A standard lot trades 100,000 units, a mini lot 10,000, a micro lot 1,000 and a nano lot 100.

 

Test Progress

This version of Forex Tester in Excel starts running and loading data by clicking the play button on the main pane (see below). Forex prices will show up in the main chart as Open-High-Low-Close candles for each data interval, and at a given pace. That speed can be changed within a scale of 1 to 10. Data can also be loaded one interval at a time with the forward button. We can also move backwards one interval at a time with the rewind button.  

Scroll bars at the bottom of the main chart allow to move forward and backwards in time (central scroll bar), and to zoom in and out the horizontal axis (scroll bar at the lower right-hand side).

Excel Forex Tester includes three technical indicators. The upper chart shows either the relative strength index (RSI) or the stochastic oscillator (KD index), while the main chart shows the standard moving average (SMA) for the last 20, 60, or 100 intervals when selected.

SMA lines for the last 5, 40 and 200 intervals are not available by default, but data is being loaded and can be added by slightly modifying the code and chart properties. These are in columns F, H, and K, in the 'Load' hidden sheet. Additional indicators can be loaded into columns T through Z, by writing the necessary code into the procedure 'Indicators', and updating the chart accordingly. This requires some VBA skills though.

 

Trade Orders

The Forex Tester in Excel allows to place either market or pending trade orders from the same dialog window by clicking 'New Order' on the main pane.

We place a market order by choosing the order type (buy or sell), selecting the volume or size, and optionally setting stop loss and take profit targets. These can be entered manually, moved up or down by the smallest unit with the spin buttons, or automatically calculated using pips.

In the same way, we place a pending order by choosing the type and size. There are four types of pending orders in Excel Forex Tester: Buy Limit, Sell Limit, Buy Stop, and Sell Stop. In the case of pending orders we need to set the target quote to enter the market. Optionally, we can set the stop loss and take profit targets. Excel Forex Tester will display symbols in the main chart for buy (blue) and sell (red) orders, along with target lines for pending orders (grey), stop loss (red), take profit (green).

Submitted orders will populate the table below the main chart and show relevant parameters along with the potential win/loss profit as trading progresses. That’s shown as profit points (or pipettes, not pips), max and lowest point values, and win/loss profit in the deposited currency.

We can edit or close orders following the command buttons for each entry in the table. Orders with stop loss/take profit targets will close automatically when hitting the target price. Closed orders will disappear from the table and move to the 'Trades' sheet, where you can see the whole trading history at a glance. 

When finished, you can save the file with other name. Otherwise, Excel Forex Tester will ask to save when creating a new project. In that case, it saves a macro-free excel file with a summary and trade history.

Learn more about the differences between Excel Forex Tester (available for download in the link below) and Forex Tester in this other page.

Click Here to download Excel Forex Tester.