spreadsheet equity formula

Hi, guys

I asked this question in Software but I think that might be the wrong forum; no takers. Hopefully this is the right forum.

I'm trying to calculate an equity column for an automated trading system in a spreadsheet.

Column G is the adjusted close (yahoo eod data), column K is my security position (+1 if I'm long, -1 if I'm short and 0 if I'm out) and column P is my equity. Row 99 is the day before row 100.

My equity cell formula is

P100 = @IF(P99<0, 0, P99*@IF(K99>-1, (G100/G99)^K99, 2-G100/G99))

I think the short-sell formula is wrong. Any ideas how I can correctly program the equity cell for a short-sell? I can almost see it, but not quite.

Thanks in advance.

 

Sometimes using the functionality of "Trace Precendents" and "Trace Dependents" would be useful.

 

Hi, OddTrader



I think you're referring to an error message? If so, that's not the problem. The formula "works" great, I just think it's a garbage output.

Short-selling is the opposite of buying long in more ways than one. With buying long, you have a finite downside and a potentially infinite upside. With selling short, you have a finite upside and a potentially infinite downside. It occurs to me that the formula I posted has a potentially infinite upside, and that's gotta be wrong.

Thanks for your input all the same.

 

Why "(P99<0,"?

What's "G100/G99)^K99"?

 

Because I'm looking at going short as well as going long, it's possible to hit that "infinite downside" and get more than wiped out. So "(P99<0," is there in case that happened in a previous cell: I just replace any negative equity with zero all the rest of the way.

(G100/G99)^K99 is my change in equity when I'm long (K99=1) or out (K99=0). It seems to work OK for those two cases.

I think I see the short-side problem. I have to refer all the way back to the entry price, not merely the previous price in the trade. Gotta figure out some fancy addressing here. :eek:

 

Perhaps simply try "ChangeEquityDaily":

(PriceEODToday-PriceEODYesterday)*Sign(LongShortQtyEODYesterday)*Abs(LongShortQtyEODYesterday)

 

What is "LongShortQtyEODYesterday"?

 

That's a QtyEODYesterday with a + sign for Long, or a - sign for Short.

 

:eek:

P100 = @IF(P99<0,0, Q99*(1 + K99*(G100-R99)/R99));
Q99 = @IF(K99=K98, Q98, P99);
R99 = @IF(K99=K98, R98, G99).



Thanks, OddTrader!