Hello new here, trading fx futures and cash for a while now. I had a question regarding backtesting an efficiency ratio (ER = price change in last 10 days/ sum of price change each of 10days) weighted exponential moving average based strategy in excel. I can download the data and then calculate the moving averages fine.The problem occurs with the backtesting.Say I want to buy if price is above the 10 day MA then comes down to touch the MA. Hold till next day close.Reverse for sell , ie price below 10day MA etc.My question is that to backtest this strategy do I need to create some loops in VBA ? Or is there something else involved ?For a simple strat like this Tradestation is actually fine. However, I have recently started to have doubts about their intraday data - and so trying a different data source.If not in Excel would a language like R etc be more amenable for this ?Thanks in advance.SB.

No you shouldn't have to create loops in VBA, that's an over kill. This is easy to do ...Depending on your horizon and frequency the number of rows can be decided by you. Make sure the frequency is consistent.Lets say you have minute data only for one day ...Column A = Timestamp Column B = PriceColumn C = EMA (Calculate your value in the first cell and then drag it down...)Column D = Check1 (If the price is above EMA set to 1 else set to 0) Using Excel's functionsColumn E = Check2 (if the price is below EMA set to -1 else set to 0)Once this is set to 1 or -1 then don't change it so you will need a nested if statement etc...Column F = Check3 (If the price touches the EMA then set to 1 else 0)Column G = if(D * F <> 0 or else if( E * F <>0 ) else do nothing)So G is your signal ...You want to do something similiar to this depending on your strategy. Do the first row then drag it down and move left to right and make sure your results are what you expect for a small sample set. You will probably need to handle error cases as well. if( iserror(A1),do this,else this) because I would imagine some of your formulas will use past values i.e. row+1 i.e. the last minutes results.What you are trying to do can be done very easily in Excel and I would suggest you use that. If you have more complicated strategies then R or Matlab would be better. You can get Bloomberg data into Excel very easily.Hope this helps, once you get more comfortable with Excel you will understand how to do things with it easily. Get a good Excel book for newbs.

Thanks muchly diamond marine. Compared to you guys I am an utter xl noob.A couple of Qs 1) The nested if function you mention - is that the one you have written for column G ?2) Do I have to construct a ddl to import the data into excel real time if I want to paper trade it ? Any clues as to how ?3) You mentioned a book. I looked at data analysis in excel for dummies - is that enough, didnt seem to have much on If functions.CheersSimon.

Lastly - in order for the signal (buy/sell) to actually do the work do I have to use the Lookback function or something equivalent ?Otherwise just having the signal is pointless.ThanksSimple(Simon).

Yes, I am referring to nested if statements in G. Excel only lets you do (2 states if else) statements as formulas. You get around this by nesting them. It can become messy and slow if you have to many in one formula so be careful. Keep it simple atleast until to get more comfortable with Excel.It depends where you are getting you data feed from. Most venders will provide a way to retrieve real time prices into Excel using RTD, DDE or COM. If they don't then look for a new vender.When I started playing with Excel I kept things simple. As I became more comfortable with it then I started to do more fancy things and complicated it more. I.e. in the initial stages I didn't do much error handling iserror() but now I do. I went through several different versions of my backtesting spreadsheet. Each time I added my functionality and simplified things at the same time. I would think you would do the same thing. You start one one spreadsheet but over time to keep improving it and replacing it with a new version. This is how you will get better and feel more comfortable with it. It might seem confusing now but in six monthes when you look back you will realize how easy it really was.Yes you will need some type of lookback check in one of your columns. This can be done easily by just referencing the row preceding it. If the value in the past is set then do this else this. So when you have a buy/sell signal the test will know and trigger the event and then wait for the next signal to unwind etc ... You might need to use 4 columns to implement what you want. In some of my backtesting spreadsheets I have 40 columns and 5000 rows. Out of the 40 columns I am really only interested in 10 of them but the other 30 helps me calculate the correct values for the 10 which I am interested in. So I just hide the 30 rows.I also use Matlab and C++ but I always use Excel in the initial stage because it very easy to visually check your calculations in realtime. Hope this helps

It helps immensely, thank you.Yes you will need some type of lookback check in one of your columns. This can be done easily by just referencing the row preceding it. If the value in the past is set then do this else this. So when you have a buy/sell signal the test will know and trigger the event and then wait for the next signal to unwind etc ... You might need to use 4 columns to implement what you want. So you mean If G3>1, then buy, 0 - the problem with this is there does not seem to be a buy or sell function. I can sort out the nested if functions ( using AND as well I think) - but do you know how I get the spreadsheet to actually buy ?Previously, I have got the return from doing say Open - Close ( on any row) - and I can do the same here presumably...ie IF G3>1 then open position at price at that point, and close at price after 10 seconds. ( which will be another column etc).Does that sound right ?Sorry to pester you...

You just have a column which is current position. 0 for no position, negative for being short and a position for being long. Then you can calculate fees and pnl in different columns. When you lookback at the previous value you know what you cannot do so the nested if will be needed.I think that's waht you are talking about.