Trying to sum only completed transactions in columns of variable length and containing extraneous info

[cseinstein]

I am trying to evaluate a stock-picking strategy based on signals for when to buy and sell. My start-date is January of 1998. Due to the nature of the signals, we sometimes start with a short position (i.e., sell without buying first), and we sometimes end with a long position (i.e., buy and never sell). I would like to eliminate both of those transactions from my totals. The problem is that the strategy results in some stocks being traded never, some being traded occasionally, and some being traded many times. So I would like to create a formula that allows me to ignore first trades that result in short positions, last trades that result in long positions, and that ignores other numbers interspersed in the same column.

I have a sample at the bottom of this post, 3 columns wide, 36 rows tall. Each transaction consists of 5 rows. If you consider the cell labeled "Price" as C1, then this is the data in the rest of the cells in the first transaction:

C2: Purchase price (expressed as an outflow, and therefore negative)​

C3: Sale price
C4: Profit or Loss on Sale
C5: Lowest close after that sale (to be entered manually, and garbage data as far as this calculation is concerned)
C6: Percent decline from C3 to C5 (all at 100% because "Lowest Close" squares are empty currently, and also garbage data for this calculation).
The numbers in Column B are either self-explanatory or irrelevant to this calculation.​

The other transactions follow the same pattern. (I have just noticed that C2 and C3 are in General format, C4 in Currency, C5 in Currency, and C6 in Percentage, in case any of that matters - though changing C2 & C3 to Currency does not seem to change anything.)

And finally, calculating manually, the sum I would hope to get from the sample column is $56.50, which would indicate a profitable strategy (for that stock, at least). In this case, we do not need to exclude the first transaction (because it starts out with a purchase) but do need to exclude the last transaction (because it ends without a sale). Sometimes we will need to do neither, sometimes both, and we will never exclude any intermediate transactions.

I have tried summing sumif's:

=sum(sumif(C2:C3,C2:C3<>0,C4),SUMIF(C7:C8,C7:C8<>0,C9))​

I continue entering sumif's until C62-64, because that is the length of the longest transaction series. I know it is possible to sum formulas because I summed MAX() formulas effectively. Unfortunately, here I get zero everytime. I am not sure if that is because I am trying to add single cells, but that shouldn't be a problem.​

I have also tried to come up with a nested if-then, but that seems unduly complicated given the variety in number of transactions. I have also tried to investigate arrays, in case that might hold a solution, but I am not familiar at all with those, and have failed to find anything there. (I've used excel for a long time, but only in relatively uncomplicated ways.)

I have searched extensively online, but as I am not sure what to look for, I may have missed something that can help me.

Thank you for any help you can give!

	Date​	Price​
LONG	2/28/1997	-0.84375
SHORT	9/30/1999	1.83929
	16	$1.00
Lowest Close after Sale
Percent Decline		100.0%
LONG	1/31/2001	-1.76571
SHORT	7/31/2001	1.09
	11	-$0.68
Lowest Close after Sale
Percent Decline		100.0%
LONG	6/30/2002	-1.36143
SHORT	8/31/2005	9.69286
	1	$8.33
Lowest Close after Sale
Percent Decline		100.0%
LONG	9/30/2005	-10.99714
SHORT	4/30/2007	24.85
	1	$13.85
Lowest Close after Sale
Percent Decline		100.0%
LONG	5/31/2007	-26.96429
SHORT	9/30/2007	16.23714
	9	-$10.73
Lowest Close after Sale
Percent Decline		100.0%
LONG	6/30/2008	-20.34714
SHORT	1/31/2012	65.07
	9	$44.72
Lowest Close after Sale
Percent Decline		100.0%
LONG	10/31/2012	-74.67171
SHORT	1/0/1900	0
	0	-$74.67
Lowest Close after Sale
Percent Decline		#DIV/0!

<tbody>
</tbody>

 

[cseinstein]

I think I have it. I expressed my SUMIF in this way:

SUMIF(C7:C8,C7:C8<>0,C9)
​

I should have expressed it this way:

SUMIF(C7:C8,"<>0",C9)
​

The second "C7:C8" was redundant. If anyone, however, wants to offer a more elegant solution (one that doesn't require individual SUMIF's, I'll take it. Thanks, and sorry for posting on what turned out to be a typo.

 

[cseinstein]

One more wrinkle: SUMIF is catching zeros that show up in the first transaction (what would be cell C2 in the example above, if the value there were zero), but not the zeros in the last transaction (what would be cell C33, on date 1/0/1900). I think I saw somewhere that SUMIF only counts the first item in a selection, but now I need to find that again - perhaps this is where arrays come in?

 

[StephenCrump]

Welcome to MrExcel!

Something like this perhaps (where 500, 501 are consecutive numbers big enough to always encompass your dataset):

=SUMPRODUCT(--(A2:A500="LONG"),--(C2:C500<>0),--(C3:C501<>0),C2:C500+C3:C501)

 

[cseinstein]

Just saw this reply, StephenCrump - will try it out and get back to you. Thanks!

 

[cseinstein]

Stephen,


Thanks! I put it in with these variations: dollar signs before the A column references, and changing 500 & 501 to 66 & 67. It works fine on these transaction patterns:


A. Long > Short, ... Long > Short (the ellipses indicating any number of identical transactions)


B. Short, Long > Short, ... Long > Short (here the formula properly discounted the gains from the first sale)


It does not, however, work with these:


C. Short, Long > Short, ... Long > Short, Long (here the formula should discount the gains from the first sale, and the losses from the final purchase)


D. Long > Short, ... Long > Short, Long (here the formula should discount the losses from the final purchase)


In both cases, I get the #DIV/0! error. In my original effort the formula ignored the gains from an initial short sale, but included the losses from a final long position that was not liquidated through a sale.


One final data point. I have stock where the transaction series consists of a single sale followed by a single purchase. The formula should discount both of those, and in fact it does, returning an answer of zero. I am not sure how that example is functionally different from example C above, but there it is. Any more thoughts would be welcome. Thanks for everything so far.


Oh, I also tried to investigate SUMPRODUCT a little more, as I was unfamiliar with it. On another site I found a formula that I reworked into this:


=SUMPRODUCT(($A2:$A66="LONG")*(E2:E66<>0)*(E2:E66))+SUMPRODUCT(($A2:$A66="SHORT")*(E2:E66<>0)*(E2:E66)).


That gave me results that were indistinguishable from the regular summing up of all the relevant cells - except for when the final cell had the value of zero (i.e., it was a transaction series that ended in a Long position), in which case I again got the #DIV/0! error. It didn't mind a zero in the initial cell (i.e., when starting in a short position), though it should have discounted that sale and did not.


Thanks again.

 

[cseinstein]

Ok, I think I found the problem: your formula seems to work now. The formula didn't like the #Div/0! error that was showing up as the final Percent Decline result for those particular columns. The exception column didn't have that. I'll double-check all the numbers once I've gotten the chance, and will let you know if there are any problems, but for now consider this solved. Thanks, Stephen.

Robert