My Tools – Chart Excel Macro


Play, enjoy, feel flow

I do quite some backtesting in Excel and I created this tool to verify whether my chart reading skills add any value on top of the quantitative edge I believe to have identified. I also use this as a training tool to improve my skills.

So here is how it works:


1. Raw data

The raw data I used for backtesting is located in F:\Data\EODData\FX30min. In this example I have used FX 30min data from Dukascopy which is structured by time, then OHLC, and in ascending order, i.e. oldest first (my equities data is usually structured in descending order, i.e. newest first). You need to adjust the path accordingly and make sure the data is structured the same way.

2. Trades

I usually get a list of trades from my backtesting in the following structure:

#Trade – Ticker – Entry Date – Exit Date – Return – Entry Price – SL – Exit Price

This data is copied into sheet “Trades”, columns A to H, overriding the existing data there.


1. Execute macro

In the sheet “Play” you have a button of the same name which starts the macro execution. If you hit it you should be directed to the sheet “ChartEntry”.

2. Entry decision

In the sheet “ChartEntry” you can see a candlestick chart of your pre-entry set-up and the Keltner channels (EMA20, 2.3) I use. Here I decide whether I would trade this set-up yes or no.

3. Evaluate visually

After your entry decision you go to the sheet “ChartExit” to see your entry, exit and SL, and you can visually evaluate whether your trade was successful or not

4. Evaluate quantitavely

Go back to the sheet “Play” and you’ll see a new line added in columns A to H with #Trade – Ticker – Entry Date – Exit Date – Return – Entry Price – SL – Exit Price. In column J you enter “y” in case you had decided to take the trade in step 2 and in the yellow box you see the statistics updated for all trade set-ups and for the ones you decided to trade.

If you now hit the “Play” button, you will go through the next round.


A lot more could be said and done, but I meant this to serve as an idea and maybe basis for others who likely need to adjust quite a few things for their own needs. And btw, I am a terrible programmer – so if others have a better way of doing this I am happy to listen.

Hope it helps.