Excel spreadsheet becomming a monster

[hellokimchi]

I've been creating a system using excel which appears to work well on historical data. Thing is, the spreadsheet is huge after calculating spreads, commissions, position sizing, signals, ect....

It is mostly all done manually, including entering EOD OHLC data. Thing is, in order to change the variables such as position sizing, MA speeds etc - I pretty much have to start again from scratch and manually go through each trade.

I know there's an easier way I just don't know how to automate many of the calculations.

Backtesting takes a LONG time done manually but the benefits are you really get to know how your system behaves. A big problem I know is I'm sure it's full of (human) errors.

I've searched the internet and have all the formulae for various MA, MACD, stoics etc, but their implementation into my cluncky error-filled spreadsheet is a real headache.

I don't undersand VBA but have a reasonable knowledge of the common excel formulas.

Can anybody help me with some common formulae used in their spreadsheets? Or maybe direct me to a website/book that can help me streamline my work with common formulas and the like used in the creation of trading sysem spreadsheets.

I'm ready to take this monster of a spreadsheet live but I think any edge will be lost amongst this clumsy beast.

 

[v..]

i heard tradestation is good for back testing, might want to it them a try

try http://groups.yahoo.com/group/xltraders/ if you need help with excel

good luck

 

[rharmelink]

I have a free open-source EXCEL add-in that has a number of functions to retrieve data from the Internet, including historical quotes from Yahoo. The add-in, documentation on its functions, and examples can be found in the files area of:

http://finance.groups.yahoo.com/group/smf_addin/

For example, if I array-enter the following formula over 7 columns and 20 rows:

=RCHGetYahooHistory("IBM")

...it would return 19 days (plus a header row) of EOD quotes for IBM. The seven columns would contain date, open, high, low, close, volume, and adjusted close.

The next release will have a few technical indicators that could use the first 6 columns of the Yahoo historical quotes as input. For example, in my testing workbook, I use the following array-entered formula to download my historical quotes:

=RCHGetYahooHistory(B4,,,,,,,,,,1,1)

Cell B4 contains the ticker symbol. The two later parameters of 1 are telling the function to return the data adjusted for splits and dividends and to sort the data in ascending date sequence (Yahoo's default is descending date sequence). Then, I use the following array-entered function to compute an exponential moving average:

=SMFTech($B$6:$G$65,"EMA",L3)

...where the $B$6:$G$65 range points to the D/O/H/L/C/V history retrieved above and cell L3 contains the period to compute the exponential moving average over.

I've been using the StockCharts education area to help develop the tech indicators. In a few cases, they have sample workbooks that can be downloaded to actually see how the indicators are being calculated:

http://stockcharts.com/education/IndicatorAnalysis/index.html

 

[hellokimchi]

OK, thanks for your replies. I didn't think to search for Yahoo Groups. Looks like there's a lot of info there for me to work with.

 

[Tuffty]

Thing is, in order to change the variables such as position sizing, MA speeds etc - I pretty much have to start again from scratch and manually go through each trade.

Using fixed simple moving averages is a pain in excel as I don't know of a way to easily change the length apart from changing each formula. However, using an EMA (Exponential Moving Ave) is simpler as all the formulas can reference one cell for the length which can be easily changed.

 

[swandro]

Let me teach you a neat trick that should make life easier for you. I will construct as simple example. I have a number in each cell in column A.

For example:

A1 1
A2 1
A3 1
A4 1

I can add these up with a formula, for example, SUM(A1:A4), giving the answer 4.

Here is the neat bit. Let us say that I want to vary the startpoint by referring to another cell, just as you would when calculating a moving average. I could put the MA value in another cell, for example B1. Let us set this to 3. Now I change my formula to this:

SUM(OFFSET(A4,-B1,0):A4). This will give an answer of 4.

What this is saying is: sum all the cells starting from cell A4 offset by the value in B4 (it is negative so it will subtract B4 rows from the position of A4, ending at cell A4. The zero parameter is saying that I do not want to offset any columns.

If you use this type of formula you can easily create moving average type columns.

Hope this helps.

 

[rog1111]

how many lines of data do you have ?

 

[swandro]

If this is a question for me, I am not sure what you mean. This formula will work with as many rows as you want. You just copy the formula down so that it exists in each row.

 

[rog1111]

sorry no it was addressed to hellokimchi, just trying to get an idea of the scale of the problem....

If this is a question for me, I am not sure what you mean. This formula will work with as many rows as you want. You just copy the formula down so that it exists in each row.

 

[hellokimchi]

how many lines of data do you have ?

It's about 5700 x about 50.....and that's just 1 sheet. I use a different sheet for each market , although due to shorter historical data, the other markets' sheets are shorter.

I just started working on scaling into and out of positions when things started to become a bit too much.

A specific formula I'd like to add which would probably save weeks of my life is to do this:

-if a value is negative, buy and vica-versa. This is a simple 'if' clause but it is also related to the following info:

-position size based on my ATR values and trade unit size
-my stops
-buying/selling at my target prices

I have all this info in the spreadsheets along with trading costs etc., I just don't know a shortcut to pull it all together automatically. I have to manually look at each day's prices and enter copy my formulas for each trade.

I've been using simple systems with trades triggered by MA, breakouts, MACD or a combination of these and I'm sure these spreadsheets have been created a 1000 times before by people with better Excel knowledge than myself.

 

[v..]

hellokimchi

KEEP IT SIMPLE !!!

i don't know much about excel but by your posts it look complicated.

all of your strategies will work and all will fail.

just pick your fav strat, back test it for about 6 months, paper trade it with a different 6 months, customize it to your risk tolerance and style.

 

[rog1111]

OK so it's not an outrageous size, hopefully you have a fast processor.

Excel is not ideal for backtesting if trades are run over several lines of data, then there's the question of stops, targets etc. You may have to resort to VBA if this is the case, but without seeing the worksheet or understanding what you are trying to do it's difficult to get a grasp on the nature of the problem. I use VB.net for all backtesting.

Excel is too big a subject to dispense much general advice, needs to be more specific I feel.

rog1111

It's about 5700 x about 50.....and that's just 1 sheet. I use a different sheet for each market , although due to shorter historical data, the other markets' sheets are shorter.

 

[homerjay]

The new Excel 2007 allows you to save files in binary format which makes it faster to work with large amounts of data, plus you can have millions of rows of data instead of the 65k or whatever the limit was in Excel 2003.

Free 60 day trail here: http://ukireland.trymicrosoftoffice.com

 

[rog1111]

Looks interesting. Hopefully this will resolve some of the Excel (lack of) speed issues, at last !

The new Excel 2007 allows you to save files in binary format which makes it faster to work with large amounts of data, plus you can have millions of rows of data instead of the 65k or whatever the limit was in Excel 2003.

Free 60 day trail here: http://ukireland.trymicrosoftoffice.com