spreadsheet equity formula

Discussion in 'Automated Trading' started by kut2k2, May 29, 2005.

  1. kut2k2


    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. :)
  2. Sometimes using the functionality of "Trace Precendents" and "Trace Dependents" would be useful. :confused:
  3. kut2k2


    Hi, OddTrader

    :confused: :confused:

    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. :)
  4. Why "(P99<0,"? :confused:

    What's "G100/G99)^K99"?
  5. kut2k2


    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:
  6. Perhaps simply try "ChangeEquityDaily":

  7. kut2k2



    What is "LongShortQtyEODYesterday"?
  8. That's a QtyEODYesterday with a + sign for Long, or a - sign for Short. :confused:
  9. kut2k2



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

    :D :D :D :D

    Thanks, OddTrader! :cool: