walking forward expectancy in excel

G

Gordon Gekko

i have known for a while that i need to tackle this problem, so here i am.

i keep track of the expectancy for all of my trades and they are grouped into systems. this way i know how each system is doing. from this i can also use the information to help size my future positions.

here is my problem: i don't want to include every single trade i've made in the calculation. as my trading improves, i don't think i should include old bad trades in the calculation. so i'd like to always use only the last, for example, 100 trades.

now if i have a column of numbers in excel, how can i have excel only use the most recent 100 numbers?

i made a very simplified excel file to help explain the concept.

if anyone can help with a solution, thanks.
 

Attachments

OK,

i thought first this was just a simple AVERAGE(B1:B100) and copy down. This is so basic though that this cannot be what you are asking. I assume that the cell that has the result is in another spreadsheet or is a static cell so I came up with this:


=AVERAGE(INDIRECT(CONCATENATE("B",counta(B:B)-99,":B",counta(B:B))))

This assumes data starts in row 1 of column B. If not then adjust the number of rows down as an offset in the above. e.g if data starts in row 10 the change it to -89 instead of 99 and to counta(B:B)+10 in the second counta statement. Counta returns the number of non-blank rows not the row number.
 
igrimsley,

I think it is as simple as your first thought. The cell references (without using $ in the cell formula) will move when copied down.

Gordon,

Try igrimsley's first formula of =Average(B7:B10), then copy it down as necessary. In your example, if you want to freeze the starting point at the first cell, say row 1 and you are not filled to your end point yet, say 100, then use =Average(B$1:B7). When you get to cell 100, take away the $ and then the "spread" will be 100 and it will use only the last 100 rows.
 
KTM,

yes, it may be that simple. The reason I came up with the convoluted solution was because anyone who has used excel for 5 minutes understands relative versus absolute addressing so I assumed that Gordon wanted to have the result appear without having to repeatedly copy down the formula. For example, if you have all the trades in one spreadsheet to which you append new data each day, but you want a summary in another spreadsheet to automatically adjust for new data.

Gordon, let us know if your question was really that facile!
 
You must log in or register to reply here.
Back
Top