Apple Numbers, my first use of Sumif...duh

Ian, this is definitely on the right track...BUT

Unfortunately we are going against the basic math involved.

See screenshot.

Row 2 = a SHORT gain

Row 3 = a LONG gain

Row 4 = a SHORT loss

Row 5 = a LONG loss

• = Correct Outcome

NG = INcorrect Outcome

NG = So in Row 2 we have a normal math outcome of a loss Entry $100 Exit $90 = a loss

but in the SHORT world that is a gain so the red ($100) should be a black $100

• = In Row 3 we have a normal math outcome of a win Entry $100 Exit $110 = a gain.

and we have a proper outcome because it follows normal math and shows a black $100

NG = In Row 4 we have a normal math outcome of a win Entry $100 Exit $110 = a gain.

but in the SHORT world that is a LOSS so the Black $100 should be a Red ($100)

• = In Row 5 we have a normal math outcome of a loss Entry $100 Exit $90 = a loss

and in the LONG world that is a LOSS so the Red ($100) is correct.

The easy way to see the problem is to check and UNcheck the Short checkbox.

So we're 50% there! I hope you are still enjoying this challenge Ian?

Thanks

Billy

Ian,

Put on the brakes, I think I found what will solve this problem here...

Using Numbers for a trading journal - Apple Community

of the two formula's I am using this one...

SWITCH(S5,"Long",F5−E5,"Short",E5−F5)

So far It seems ok, I will put it together and rearrange the spreadsheet

and post so you and others can understand it.

Will post ASAP

Billy

Ian,

Just thought you may want to know I found a very simple way by creating another column

with a simple (=-ColumnLetter)...Change values in a column from positive t… - Apple Community

seems to work well.

Again thanks

Billy

Ian,

I thought I would be sending you the final, however the new drop down menu to select if it's a Short or Long does the calculation however is very slow 3 clicks to do what the checkbox does in one. I used the SWITCH(S5,"Long",F5−E5,"Short",E5−F5) formula for Col K and tried to change the "Long" to "False" and the "Short" to "True" but it didn't work. Any thoughts. I added some notes on the latest screenshot.

For the screenshot I separated columns (Long Gains, Short Gains, % Long and % Short) I am questioning their purpose. I would like to know the percentage but that's two columns and same for Long and Short gains. Analysis of trades is important but 4 columns trying to understand how to have only 2 to give info.

By the way I posted some info by replying to me, where here, I am replying to you. Do you get a notice for both?

*For some reason this website is not allowing me to upload the screenshot, will add later hopefully it will work then.

Hi Ian, 

Here is a minimized table I have been going nuts so I needed to clear the clutter.

CALCULATIONS ARE…

Col E LONG Gains IF(B2,0,I2−H2)

Col F SHORT Gains IF(B2,I2−H2,0)

Col G P/L Per Trade IF(B2,0,D2−C2)

Col H Invested. C2×$A2

Col I Profit/Loss. D2×A2

SEE VALUES SET ON SCREEN SHOT

Rows 2 & 3 are profitable trades

Rows 3&4 are losses

If you change B2 to a short by checking the checkbox in Col B

The value shifts to the SHORT col F (correct), BUT that would be a LOSS for a SHORT

So that should be a Red ($500).

Cell F3 should be $300 in black.

Cell F5 it should be ($100) in red.

So even though the checkbox works correctly in shifting the number to

The Short column, it doesn’t make it a Loss.

So I guess you can say as soon as you check that box and it turns RED

That is what should happen to the number, If it is a loss (Buy High/Sell Low).

I hope that is clear Mate, just saw that you’re an Aussie.

Billy

Hi Ian,

Sorry for my typo, I mean D5.

So then you'll see the $200 reduction, instead of what I think should be only $100?

The checkbox column D toggles between adding/subtracting from the appropriate column

either the Long or Short column.

At one point I thought what if when the value is a $100 win in the Long column and then instead

of a ($100) deduction being put in the Short column it puts a $0 then it would only be a $100 change.

Of course if it is a True loss then it would be correct to have ($100) to calculate properly

Thanks for the super quick reply Ian.

Billy

Hi Ian,

For simplicity I changed Col P formula to (E2×$B2), Col Q stays at (F2×B2). Same results just simpler.

*Previously needed to round off Qty for trade blocks of $10k, but now is handled outside of this spreadsheet.

*My screwup was I started to write out all the info and then I said I should get rid of unnecessary columns

which changed the formulas. SORRY!

So Col P calculates Col E Entry Price x QTY (E2×$B2)

Col Q calculates Col F Exit Price x QTY (F2×B2)

Col K calculates the difference between Col P and Col Q for Longs (Q2−P2)

Col L calculates the difference between Col P and Col Q for Shorts (P2−Q2)

  *Notice the calculations are reversed to force a positive (black) value if a win in short.

This makes me think about what I said before…

“At one point I thought what if when the value is a $100 win in the Long column and then instead

of a ($100) deduction being put in the Short column it puts a $0 then it would only be a $100 change".

Billy

Ian,

I am not opposed to having a different way to do what I need. It's very possible that I am not doing things the most efficient way. So if you're saying to yourself what is this guy doing, please tell me. Thanks for your efforts. Billy

Ian, Multitasking has gotten the better of me, my apologies!

I am currently going back to your simple GREAT formula, (which also allows me to get back to the checkbox) because this new one I found has the same original problem, I think I was blinded by my eagerness to get this over with. I will post ASAP.

Thank you again. Billy

Ian

You solved a very big problem in this spreadsheet and for that I am very grateful.

Thanks for all your help

Billy