transition to excel.

[combotrader]

I have a 20 day breakout strategy for cable with a 1.5 ATR stop or 200 points, whichevr is greater. I have backtested this manually from 1990-2006. It generated 211 trades with an expectancy of 0.74 and a reliability of 61%. I have not taken into account slippage and the cost of trading - which will make the expectancy much lower. But I would be happy wiht an expectancy of around 0.5 - this system makes its money on 4-5 good trades in a year.

I also used a standard MACD for divergences and a 200d sma for s/r.

The results are all on excel, entered manually. Esignal was used as a datasource, not feeding into excel. I would like to know, if at all, how this strategy can be programmed into excel. My knwledge of excel is basic to say the least🙄

...so any pointers would be helpful, even if its go and read Excel for Dummies...

The ultimate aim is buy some software and do a monte carlo simulation.

Thanks in advance..
CT

 

[A Dashing Blade]

Define "program" in this context
You want the whole thing coded in vba and a single cell telling you "BUY"?
OR (the other extreme)
You want a load of formulas on a sheet and you manually enter EoD data?

Either one is perferctly possible.

 

[combotrader]

Define "program" in this context
You want the whole thing coded in vba and a single cell telling you "BUY"?

Yes, ADB, that is right. Basically, I think I`m refering to coding in VBA. Now, how does one learn ?

Cheers
CT

 

[grantx]

CT,

Are you looking to calculate the MACD and 200d sma in Excel from the raw data (daily values?) in Excel?

If so, this is very simple without vba.

I would never question DB’s expertise but for yourself, I would suggest doing the calcs directly into an Excel sheet rather than via vba.

Whilst vba has many advantages, it is less intuitive and not necessary for simple calcs/formulae. Further, it will take much longer to learn.

If you wish to learn Excel and/or VBA I would recommend going for out-of-date books, eg Excel 97. These can be picked up for a song compared to the latest versions. The only possible disadvantage is these will not be cutting-edge. However, this is not a disadvantage because they all cover the fundamentals, providing for 99% of anyone’s needs.

For Excel, consider Running Microsoft Excel 97, Dodge, Kinata, Stinson; for VBA, Excel for Windows 95: Power Programming with VBA, J Walkenbach

Grant.

 

[jezgw]

have a look at

http://www.financial-edu.com/ez-files-technical-indicator-library.php

 

[grantx]

Jezgw,

For free stuff, do a google advanced search: Excel technical analysis

Here's acouple of examples:

http://ta-lib.org/hdr_lnk.html

http://www.webcabcomponents.com/office/addin/financial.shtml

Grant.

 

[samuelhogg]

Companion spreadsheet to the book 'Technical Analysis from A to Z' by Steven B. Achelis

http://debry.com/a_to_z_companion_spreadsheet.htm

Samuel

 

[combotrader]

Thanks for all your replies guys.

Grantx, do you mean entering the indicatory values into excel myself, or using a formula for them to be worked out ?

Thanks
CT.

 

[grantx]

CT,

Enter a formula yourself on an Excel sheet to calculate the various measures/indicators. For example, to work out a 5-day moving average on daily values (due to formatting constraints the figures below are not quite in columns. Col A no's are those 100 - 111, Col B refers to the =AVERAGE(....) formulae:

Column A Column B
Row 1 100
Row 2 102
Row 3 105
Row 4 106
Row 5 107
Row 6 104 =AVERAGE(B1:B6)
Row 7 105 =AVERAGE(B2:B7)
Row 8 106 =AVERAGE(B3:B8)
Row 9 108 =AVERAGE(B4:B9)
Row 11 110 =AVERAGE(B5:B10)
Row 12 111 =AVERAGE(B6:B11)
etc

Note when calculating moving averages, the first value starts in the row, number (of days)+1, eg first value for 5-day = (in) 6th row.

It is only necessary to enter the first line on row 6 – subsequent values will be produced by simply copying the formula into subsequent rows. Even if you have thousands of rows, all that’s needed is to click the first value in row 6, highlight the rows, and press Copy. That’s it – all the ma’s will be appear.

All you need are the relevant formulas. If you have difficulty converting these into Excel format – they’re not all so straightforward – just ask; it’s no big deal.

Grant.

 

[combotrader]

Grant

Thanks a bunch for this. Looks pretty stforward as you have done it..waits to be seen if I can do it!!

ALso getting hold of a couple of the books you mentioned.
Did you get the formula`s from a book. They`re obviously intuitive - but EMA`s and adaptive MA`s will need a bit more work:?:

Thanks for all your help.

Cheers
CT.🙂

 

[jezgw]

grantx,thanks for the free links.
Wouldn it be better to do this stuff in c# or the free copy of ninja?

 

[grantx]

Jezgw, CT,

The manual for my quote system has a pretty extensive section re technical indicators, with descriptions and formulae. Send me your e-mail address and I'll send you a copy.

The format is pdf, and it is a big file - c 860 pages. However, you can just print the pages reflecting your interests. The technical indicator section is around 260 pages.

Grant.