Calculate long/short positions

[Megamannen]

Hi I’m an excel newbie. I have Mac excel 2010.

I’m trying to perform trading backtesting and calculate long and short positions.

In column A I have Google stock closing price
In column B I have “BUY” or no information at all
In column C I have “SELL” or no information at all

In D2 I have a sum of 10 000 dollars. Let’s say there is a BUY in B3 and a blank in C3. In this case I would perform this calculation =IF(B3=”BUY”,C3=””),(A4/A3)*D2,D2). If there was a blank in column B3 and a SELL in C3, I would perform this calculation =IF(B3=””,C3=”SELL”),(A3/A4)*D2,D2).

But here is my problem: Sometimes a BUY and a SELL collides on the same row level. And sometimes neither a BUY or a SELL appears.

Is there a neat way to solve this?

 

[oldbrewer]

why on earth would you buy 1000 google shares and sell 1000 google shares at the same time ?

 

[Megamannen]

I wouldn't. I'm sorry if I was unclear.

If there is a BUY i B3 and a blank in C3 I would buy. But if there was a blank in B3 and a SELL in C3 I would sell. Of course this could not happen at the same time.

If there is a BUY in B3 and a SELL in C3 I would not do anything. I use various trading strategies and sometimes they contradict each other. My way of dealing with this is to stay passive and only change position if there is a clear SELL or BUY.

 

[AlphaFrog]

Try something like this...

=IF(B3&C3="BUY",(A4/A3)*D2,IF(B3&C3="SELL",(A3/A4)*D2,D2))

 

[Megamannen]

Try something like this...

=IF(B3&C3="BUY",(A4/A3)*D2,IF(B3&C3="SELL",(A3/A4)*D2,D2))

Thank you sir for your response. It does partially solve my problem.

Although whenever a situation arises where there is a SELL and a BUY on the same row level, it is calculated just like I was liquidating my position. I would like to perform the previous calculation until there is a clear reverse signal, i.e. a BUY and a blank or a blank and a SELL.

Please bear with me if my english is terrible. It is a second language.

 

[AlphaFrog]

Your english is very good.

I don't know how the previous calculation is determined or labeled, but maybe something like this? You will have to modify it to add the calculation.

=IF(B3&C3="BUY",(A4/A3)*D2,IF(B3&C3="SELL",(A3/A4)*D2,IF(B3&C3="BUYSELL","Previous calculation here", "Neither")))