Profile and loss calculation?

[QuantEdge]

hi exclers,
how you I calculate a simple "profile and loss" base on the buy/sell order from a price array?
any ideas or suggestions, see attached sheet... thx

[Logit]

.
Disregard

[QuantEdge]

thx Logit , but its got to be a really solution (custom formula) to the attached sheet!

[MrShorty]

I can't find anything regarding "profile and loss" on the internet. Did you mean, as logit assumed, profit and loss? I am not in financials, but everything I encounter for calculating profit/loss is simply to add up "income" (whatever that means for your specific scenario) and add up "expenses" (again, whatever that means for your scenario), then take the difference of those two values. Your sample spreadsheet is fairly cryptic with only "buy" and "sell" indicators. How would you compute "income" from this data? How would you compute "expenses"?

[FlameRetired]

Judging from the formulas in column C I took a guess.

=SUMPRODUCT(--($B$1:$B$30="SELL"),$A$1:$A$30)-SUMPRODUCT(--($B$1:$B$30="BUY"),$A$1:$A$30)

[QuantEdge]

does not see to work Flame....

I need to calculate the profile/loss when it BUY (first BUY) and when it turns SELL, calculates the prices of BUY to SELL to get the profile or loss! etc...
from the SELL signal the formula needs to look back down the array, at which was the BUY signal.

keep in mind I need a very simple formula to active this as I will be using thousand of rows... keep try I got a hand full of stars ****** to give out here)

[FlameRetired]

Try a helper column in D. In D1 and fill down

=(B1="SELL")*(B2="BUY")

Then in C1 and filled down.

=IF(D1=1,A1-INDEX(A2:$A$30,MATCH("SELL",B2:$B$30,0)-1),"")

Sum column C to get the total.

You may have to re-format column C to the same as A.

Does this do what you want?

[MrShorty]

"does not see[m] to work" does not help us very much. Why doesn't it work? What do you want it to do differently? Can you provide any explanation of how you want to perform this calculation -- maybe even some worked examples in your spreadsheet? Flameretired's formula seems to assume that everything marked "sell" is income, and sums those values up (I would have used SUMIFS() rather than SUMPRODUCT(), but the idea is the same). He then assumes that everything marked "buy" is expense, and sums those values up. Profit/loss (still not sure what "profile" is or if it is different from profit) is then the difference between the two sums.

If you are looking for a "moving" profit loss, it seems like it should be the same formula, but with a different mix of relative and absolute references. I can't make a recommendation, because it is not clear to me if this is the case, or exactly how you want to do this.

[FlameRetired]

Me neither. These are all my best guesses.

[QuantEdge]

thx, FlameRetired
but can't seem to get your formula to work?

and yes MrShorty I looking for that "moving" profit loss... any ideas.

[MrShorty]

I don't yet understand exactly how you want to do this moving profit/loss, so I have no ideas. I still expect a difference of SUMIFS() or SUMPRODUCTS() like FlameRetired's first suggestion, but I don't understand how you intend to define the "window" for you moving profit/loss.

[FlameRetired]

@ QuantEdge

That's because the former column D is now column C. Try in D1 and filled down. It returns what you had in the former column C.

=IF(C1=1,A1-INDEX(A2:$A$30,MATCH("SELL",B2:$B$30,0)-1),"")

[QuantEdge]

thx FlameRetired,
works really well and big thx...

[QuantEdge]

FlameRetired,
PS is there any way to optimize (short/simpler etc) your formula, as so to make it run faster as I will be using it on a massive data set thousands of times?
IF anyone can optimize I would be very grateful ** thx

[FlameRetired]

FlameRetired,
PS is there any way to optimize (short/simpler etc) your formula, as so to make it run faster as I will be using it on a massive data set thousands of times?
IF anyone can optimize I would be very grateful ** thx

At the moment nothing comes to mind. I'll give it some thought though.

RE: "massive data set thousands of times". Could you offer some hard numbers ... a range?

[FlameRetired]

Good to hear, and you are welcome. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[FlameRetired]

Will the data in column B always start with a "BUY"?

[FlameRetired]

I have been playing with this and have a few ideas.

shorter/faster/simpler comes down to "pick 2". I've focused on faster/simpler (uses 5 helper columns ... which are fast). I don't think I can do more than that with formula.

Am awaiting replies to my last two posts 16/17.