The Excel Trading Trainer or we can call it simply trainer is a semi-automated tool to simulate security price changes over time. It provides basically:
- Automated tick generator on demand.
- Price, volume and trend information generation.
- Elemental Charting generation for each tick.
- Universal financial data parser.
- Trading operations simulation.
- Integration with Microsoft Office Excel.
The current status of the tool is under development, but the most important features are already in an acceptable progress status. It can be already used for testing trading strategies.
Motivation and purpose
Whether it’s either a new trading manual, strategy, indicator or algorithm to be reviewed, in many cases manual testing is a must. Having a tool that can generate ticks from any financial data you provide lets you adapt the maket timing to your own pace.
You will not have to wait until the next day to verify that your last order (if you trade daily prices) will have the outcome expected. You only have to click on a button and, there it is, the next day’s price is generated with real data from real securities, including stock markets, foreign exchange quotes (FOREX), commodities, criptocurrencies like bitcoins, financial indexes or even mutual funds. Anything that has a price fits within the tool.
With just one click the new tick is generated independently of the timeframe: one second, five minutes, one hour, one day, one week. You get the next quote exactly when you need it and until that moment you only can see the previous price evolution, charts and values.
I had a price prediction model idle for more than one year due to time restrictions because I didn’t had the right tool to test it. Also spent over three years trading with demo accounts one day after another. A candlestick cannot replace real-time trading, but it should be enough at least to test your system before you actually want to enter into demo trading with real-time data. You would save probably years and money. Same way demo trading is far from being the same as live trading, but without profitable sustained demo trading over months no one should even think about start spending one single cent on a live trading account. Therefore a simple ticker that displays real market data is a basic and convenient tool to perform a first approach to actual financial trading.
The Excel Trading Trainer is hosted on Github. It can be accessed by clicking on the donwload link below:
The file format is XLSM or Excel 2007 format with macros enabled. Most of the functionalities are written in Visual Basic for Applications. When opening it for the first time, the enable macros button must be clicked if displayed. This occurs the first time an Office document with macros is opened.
For instructions about how to use it, please go to the next page by clicking on the link below.
The main github project page is: https://github.com/gonzsanc/Excel-Trading-Trainer
The tool has three main sheets:
- data: Used to paste the financial time series data for generating the ticks.
- iterator: It’s the main sheet, all the funcionalities are executed on this page.
- configuration: The configuration sheet is used to change the parameters of the tool. It must be used with caution.
The figure above shows the three main sheets aspect.
The data sheet
To change the data, you must first delete the existing data, and then copy-paste the new data directly from the file source. Do not remove columns or rows, since it may affect to the internal configuration of the sheet causing it to stop working.
Always paste only values on the sheet. The date columns must have always text format. The other columns must have a numeric type format.
If your data contains decimal numbers, be sure to replace those by the ones that your Excel configuration uses, otherwise Excel will parse the figures as text strings and the charts and formulas won’t display any numeric value. Remove any thousands separator of your data before pasting on the data sheet.
The iterator sheet
The charts area contains three charts, two OHLC charts with candlesticks, one with the latest bars and the other with a longer period. The third chart is the trend chart that displays a line chart of the daily close price and three simple moving averages of respectively 10, 20 and 50 days. The trend chart also includes a linear regression line for the close price and the linear regression formula corresponding to the current chart data. The following image shows the trend chart more in detail:
The info/reset area displays basic data information and includes the reset charts button that is used to reset the charts to the default values and prices. When the reset charts button is clicked, the data pointer is moved to the default bar (usualy the bar in the 200th position) and all the charts and controls area are reset accordingly. The reset ops button will remove all the operations introduced on the operations panel.
The controls area is used to move bars (generate ticks) and to execute orders. The supported orders are buy and sell. An open operation may be closed by clicking the opposite operation. This section is still under development and is not completely functional. It is adviced to insert orders always clicking the buy and sell buttons.
Another functionality of the controls area is the move bars button. It will generate a tick each time it is pressed. It supports both negative and positive numbers that must be indicated in the Positions cell. The target cell is just used to insert a position and calculate how many positions the current bar is far from it.
The displays within the buy and sell buttons section include volume indicators:
- VOL INCR: Relative volume difference between the current and the past bars .
- PREV. Relative volume difference between the last bar and the third past bar (past bar’s VOL_INCR .)
- VOL_DIFF: Differential of the current bar increment over the previous bar’s increment. It’s the marginal increment change.
The operations panel will display the orders executed from the controls area. Several of the cells have a limited list of possible values. This area and its interaction with the controls area is still in development.
The config sheet
This sheet contains critical configuration parameters. The green ones will adjust the chart configurations. This sheet should be manipulated with special care.
The indicators sheet
Provides extended information about the financial data. The information is provided with absolute, relative, dynamic, technical and cross-feature magnitudes.
Financial data files
I have uploaded 400Mb of financial data to this Google Drive Link:
Some files have data with errors and you might need to:
- Replace “nan” string by “0” (zero).
- Replace the decimal symbol (comma) by the one your system uses for Excel.
The data file separator is tab so that it can be directly copied from the text file and pasted on the Excel sheet.
The data is compressed using 7z format, you may need to install the 7z uncompressor to access it.