Excel spreadsheet becomming a monster

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.

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

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/Ind...sis/index.html

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.

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.

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.

how many lines of data do you have ?

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.