Calculating results in Excel

H

hans123

I want to calculate some results in Excel. I have a column with daily changes of closing prices and a column with a signal value (Long, Short or Neutral). If the signal still is the same then I want to add (in case of Long) the daily change to the previous value or subtract (in case of a Short) the daily change to the previous value.
My data looks like:

A B C
-23 Short +23
-45 Short +68
+5 Long +73
+7 Long +80
+132 Long +212
+34 Long +256
-4 Long +252
+54 Long +306
-34 Neutral +306

Does someone know how I can calculate column C in Excel?

Thanks.

Hans
 
I want to calculate some results in Excel. I have a column with daily changes of closing prices and a column with a signal value (Long, Short or Neutral). If the signal still is the same then I want to add (in case of Long) the daily change to the previous value or subtract (in case of a Short) the daily change to the previous value.
My data looks like:

A B C
-23 Short +23
-45 Short +68
+5 Long +73
+7 Long +80
+132 Long +212
+34 Long +256
-4 Long +252
+54 Long +306
-34 Neutral +306


d2=if(b1=b2,abs(a1)+d1,d1)

note u might have to change "," to ";" depending on ur language settings
 
you can also use conditional formatting statements

it sounds like you're doing something similar to a basic black jack +/- counting system but your C column seems to be adding even when the B column says short.
maybe I'm missing something.

also, C6 should be 246 not 256 if you're adding A6(+34) to C5(+212).
 
Quote from hopback:

you can also use conditional formatting statements

it sounds like you're doing something similar to a basic black jack +/- counting system but your C column seems to be adding even when the B column says short.
maybe I'm missing something.

also, C6 should be 246 not 256 if you're adding A6(+34) to C5(+212).
More...
You're right, it should be 246. Sorry for that mistake.

Basicly I had one short serie and one long serie. I want to add the total of the short serie to the total of the long serie. If the daily result is negative and I was short the result of the trade should be positive.

Hans
 
Quote from vak001:

d2=if(b1=b2,abs(a1)+d1,d1)

note u might have to change "," to ";" depending on ur language settings
More...
I don't want to make an addition when there is a neutral signal. I think this code don't make that comparison.

Hans
 
Another option might be:

A B C
-23 Short
-45 Short +68
+5 Long
+7 Long
+132 Long
+34 Long
-4 Long
+54 Long +228
-34 Neutral 0

Hans
 
Instead of using Long, Short, Neutral you could use 1, -1, 0, thus:

c2 = (a2 * b2) + c1

You can create constants in Excel (e.g., Long = 1, etc.), so you still use the names in Column B:

1. On the Insert menu, point to Name, and then click Define.

2. In the Names in workbook box, enter the name for the formula.

3. In the Refers to box, type = (equal sign), followed by the formula or the constant value.
 
Quote from hans123:

You're right, it should be 246. Sorry for that mistake.

Basicly I had one short serie and one long serie. I want to add the total of the short serie to the total of the long serie. If the daily result is negative and I was short the result of the trade should be positive.

Hans
More...

I guess I'm not following you.
Are you saying it's not cumulative?

"If the daily result is negative and I was short the result of the trade should be positive."

So, if A is negative and B is "SHORT" then C becomes positive?
What would make C negative?

here's a formula for a cumulative approach, the plus/minus idea I mentioned. Although it sounds like it's not what you're looking for.

I left your column C as is and used column D.

Based off of your C1, I started with:
D1 =ABS(A1)
D2 =IF(B2="long",(D1+ABS(A2)),IF(B2="short",(D1-ABS(A2)),D1))

then drag the formula down the column.
 

Attachments

Quote from hans123:

If the daily result is negative and I was short the result of the trade should be positive.

Hans [/B]
More...

is the daily result column A or C. I thought A was the daily change.

Are you saying you want to track the days you were right on market direction? long on up days and short on down days?

If this is the case, why is C equal to the entire daily change? Are you in your position from open to close?
If not, are you trying to track possible gains on the days you are right?

I may be entirely off base. Just trying to fugure out exactly what you're trying to do.
 
I post my original file. I want to calculate my results based on my position. My position is short (-1). neutral (0) or long (1). When I am short and the result is negative, my trading result should be positive. When I am long and the result is negative I want to subtract it from the total I have at the moment.

Hans
 

Attachments

You must log in or register to reply here.
Back
Top