Excel spreadsheet becomming a monster

This is a discussion on Excel spreadsheet becomming a monster within the First Steps forums, part of the Reception category; I've been creating a system using excel which appears to work well on historical data. Thing is, the spreadsheet is ...

Reply
 
LinkBack Thread Tools Search this Thread
Old Dec 22, 2006, 1:48am   #1
Joined May 2005
Post 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.
hellokimchi is offline   Reply With Quote
Old Dec 22, 2006, 3:34am   #2
v..
 
4 Posts
Joined Oct 2004
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
v.. is offline   Reply With Quote
Old Dec 22, 2006, 3:49am   #3
Joined Oct 2005
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
rharmelink is offline   Reply With Quote
Thanks! The following members like this post: the blades
Old Dec 22, 2006, 6:47am   #4
Joined May 2005
hellokimchi started this thread 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.
hellokimchi is offline   Reply With Quote
Old Dec 22, 2006, 10:59am   #5
Joined Oct 2003
Quote:
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.
Tuffty is offline   Reply With Quote
Old Dec 22, 2006, 12:00pm   #6
Joined Aug 2003
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.
swandro is offline   Reply With Quote
Thanks! The following members like this post: the blades , Tuffty
Old Dec 22, 2006, 1:47pm   #7
 
rog1111's Avatar
Joined Jan 2004
how many lines of data do you have ?
__________________
He who knows much about others may be learned, but he who understands himself is more intelligent. He who controls others may be powerful, but he who has mastered himself is mightier still. Lao Tse
rog1111 is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Spreadsheet-if any use Burney Psychology, Risk & Money Management 4 Dec 26, 2008 4:12pm
Excel~Spreadsheet~Financial site Trdr Trading Software 2 Apr 19, 2007 3:42am
Cookie Monster GammaJammer Techies Corner 5 Nov 12, 2004 8:56am
Trading SpreadSheet jklondon Psychology, Risk & Money Management 7 Feb 24, 2004 11:10am

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)