Summing qualifying data

[Dallman Ross]

The following has me perplexed. I would have thought SUMIF
would be what I want, but I can't get it to work.

I have a worksheet with some information about stock transactions.
Col. B is the Quantity (shares purchased). Col. C is Date Bought.
Col. F is Date Sold. Col. M is Days Held, but we probably don't
need that for my question, and anyway, that's just F minus B.

I want to know how many total shares I owned at the time of
each new purchase.

Here's a sample. It's sorted by Column F, then Column C:

B C F M
Quantity Bought Sold Days Held
row -------- ------ ------ ---------
4 100 19-Feb-02 9-Jan-03 324
5 200 19-Feb-02 9-Jan-03 324
6 100 11-Mar-02 9-Jan-03 304
7 100 11-Mar-02 13-Jan-03 308
8 200 12-Mar-02 13-Jan-03 307
9 300 12-Mar-02 5-Feb-03 330
. . . .
47 200 31-Jan-03 15-May-03 104
48 300 25-Feb-03 15-May-03 79



Let's suppose the data is expressed between rows 4 and 48 inclusive.

I tried something like this:

=SUMIF(F$4:F$48,"<=C4",B$4:B$48)

And I tried dragging that down. The criteria (in quotes) is what
doesn't work, and it also doesn't increment when I drag.

What I want is to know is the max shares owned during each row's
day-spread. So at any time between 19 Feb 02 and 9 Jan 03 (inclusive) I
owned 100 + 200 + 100 + 100 + 200 + 300 shares, and that total could in,
say, cell Z4. The Z5's formula would happen calculate the same spread,
because the dates are identical. Z47 would show that I owned, max,
500 shares during the period from 31 Jan 03 through 15 May 03.
Hmm, the average shares owned during the time period would be cool
too, but let's get the first problem solved first. (The we
can work on graphing it, which I'm also having some trouble with,
but more on that later.)

Thanks for any help.

 

[Frank Kabel]

Hi
try
=SUMIF(F$4:F$48,"<=" & C4,B$4:B$48)

Frank

 

[RagDyer]

Not into stocks at all, but just curious about your thinking when you
constructed your formula.

Since column F is "SoldDate", and column C is "BoughtDate", your formula is
attempting to identifying rows where the "SoldDate" is less then or equal to
the "BoughtDate".

That means you can sell a stock *before* you bought it ?

Is this a normal procedure, or one that perhaps the SEC might frown upon
?<g>

Serious question.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


The following has me perplexed. I would have thought SUMIF
would be what I want, but I can't get it to work.

I have a worksheet with some information about stock transactions.
Col. B is the Quantity (shares purchased). Col. C is Date Bought.
Col. F is Date Sold. Col. M is Days Held, but we probably don't
need that for my question, and anyway, that's just F minus B.

I want to know how many total shares I owned at the time of
each new purchase.

Here's a sample. It's sorted by Column F, then Column C:

B C F M
Quantity Bought Sold Days Held
row -------- ------ ------ ---------
4 100 19-Feb-02 9-Jan-03 324
5 200 19-Feb-02 9-Jan-03 324
6 100 11-Mar-02 9-Jan-03 304
7 100 11-Mar-02 13-Jan-03 308
8 200 12-Mar-02 13-Jan-03 307
9 300 12-Mar-02 5-Feb-03 330
. . . .
47 200 31-Jan-03 15-May-03 104
48 300 25-Feb-03 15-May-03 79



Let's suppose the data is expressed between rows 4 and 48 inclusive.

I tried something like this:

=SUMIF(F$4:F$48,"<=C4",B$4:B$48)

And I tried dragging that down. The criteria (in quotes) is what
doesn't work, and it also doesn't increment when I drag.

What I want is to know is the max shares owned during each row's
day-spread. So at any time between 19 Feb 02 and 9 Jan 03 (inclusive) I
owned 100 + 200 + 100 + 100 + 200 + 300 shares, and that total could in,
say, cell Z4. The Z5's formula would happen calculate the same spread,
because the dates are identical. Z47 would show that I owned, max,
500 shares during the period from 31 Jan 03 through 15 May 03.
Hmm, the average shares owned during the time period would be cool
too, but let's get the first problem solved first. (The we
can work on graphing it, which I'm also having some trouble with,
but more on that later.)

Thanks for any help.

 

[Dallman Ross]

Not into stocks at all, but just curious about your thinking when you
constructed your formula.

Since column F is "SoldDate", and column C is "BoughtDate", your
formula is attempting to identifying rows where the "SoldDate" is less
then or equal to the "BoughtDate".

That means you can sell a stock *before* you bought it ?

No, not in these charts, although it is indeed a standard practice to
sell stocks before one buys them back. It's called "selling short."
I do that sometimes too, but it so happens that I don't in the account
that I'm trying to develop this formula for.

No, my thinking is along the lines of that, lower down in the chart some
buy dates will occur after earlier sell dates. It may well be that my
attempted formula was completely screwy. But all I really want to do is
this:

Suppose I buy 200 ABC on 1/1 and sell 200 on 1/6.
Now suppose I buy 400 ABC on 1/3 and sell all of that on 1/9.
And now suppose I buy 300 ABC on 1/8 and sell it all on 1/13.

What's the max shares I've owned at any one time? What's the max number
of shares I've owned during any part of the time that I owned the first
200?

Let's not worry about the case when I don't sell all I bought,
because on my charts each line reflects a completed sale of
the quantity indicated.

I want to see what the biggest position is that I carried in that issue
over the timespan charted. Each row should show the biggest position
owned during any part of the date range seen in that row, as a result
of the formula I want. So on the first row of what I've indented, the
answer would be 600 shares. On the second line, the answer should be
900 shares. On the third line, 700 shares. I can then eyeball the
column (or sort the chart) to see that the biggest position held overall
was 900 shares.

Could really use some help with this.

An alternative I wouldn't mind, in case it's easier, is: on each
buy date, what's my total current position?

 

[RagDyer]

Starting with this approach:

<<"An alternative I wouldn't mind, in case it's easier, is:
on each buy date, what's my total current position?">>

Col B - Quantity Purch.
Col C - Date Purch.
Col D - Current Position
Col E - Quantity Sold
Col F - Date Sold

Data starts Row 4

Leave D3 EMPTY
Enter this in D4:

=IF(C4>0,SUM($B$4:B4)-SUMPRODUCT(($F$4:F4<=C4)*($E$4:E4)),D3-E4)

Drag down about 10 rows to copy, which will return zeroes.

Now, add some data, and see if this will suffice.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Not into stocks at all, but just curious about your thinking when you
constructed your formula.

Since column F is "SoldDate", and column C is "BoughtDate", your
formula is attempting to identifying rows where the "SoldDate" is less
then or equal to the "BoughtDate".

That means you can sell a stock *before* you bought it ?

No, not in these charts, although it is indeed a standard practice to
sell stocks before one buys them back. It's called "selling short."
I do that sometimes too, but it so happens that I don't in the account
that I'm trying to develop this formula for.

No, my thinking is along the lines of that, lower down in the chart some
buy dates will occur after earlier sell dates. It may well be that my
attempted formula was completely screwy. But all I really want to do is
this:

Suppose I buy 200 ABC on 1/1 and sell 200 on 1/6.
Now suppose I buy 400 ABC on 1/3 and sell all of that on 1/9.
And now suppose I buy 300 ABC on 1/8 and sell it all on 1/13.

What's the max shares I've owned at any one time? What's the max number
of shares I've owned during any part of the time that I owned the first
200?

Let's not worry about the case when I don't sell all I bought,
because on my charts each line reflects a completed sale of
the quantity indicated.

I want to see what the biggest position is that I carried in that issue
over the timespan charted. Each row should show the biggest position
owned during any part of the date range seen in that row, as a result
of the formula I want. So on the first row of what I've indented, the
answer would be 600 shares. On the second line, the answer should be
900 shares. On the third line, 700 shares. I can then eyeball the
column (or sort the chart) to see that the biggest position held overall
was 900 shares.

Could really use some help with this.

An alternative I wouldn't mind, in case it's easier, is: on each
buy date, what's my total current position?

 

[Dallman Ross]

In <[email protected]>, Frank
Kabel <[email protected]> spake thusly:

Wow. That actually works, in that the formula is valid and giving me
some numerical results. Thanks for the tip! Now I have found out
that my logic was wrong, however. That formula doesn't tell me what
I wanted to know. Hmm, how do I find the max share owned during the
row's date range?

I was sure this was a good approach (although I have yet to see that
ampersand documented anywhere! Where do I find out more about such
operators?); and I've thought about it while lying in bed, for a
long while; and, by $DEITY, but this is getting close:

=SUMIF(C$4:C$48,"<=" & F4,B$4:B$48)-SUMIF(C$4:C$48,"<" & C4,B$4:B$48)

That actually gives me the total position owned within the *full*
date-range expressed on a given row. Output looks like this:


7800
7400
7400
6900
6400
6800
6800
6100
6100
5900
8500
8000
7800
7800
8100
. . . .


and so on. Very good, indeed. (So my original thought on a workable
approach was not so wrong; just my formula was insufficient.)

I still would rather have, though, not the total owned during the
full date range within a row, but the total owned during any part
of the date range within a row. Hmm. Frank? Or anybody?


Dallman

P.S. Merely as a point of information, and to get a feel for how
those numbers above play out, during 2003 I traded (realized gains,
i.e., closed-out trades) a total of 11,100 shares of that stock
(much of which was bought in 2002). The lats number shown above
is the highest position held for the whole of any one date range.
That was between 19-Apr-02 and 3-Mar-03. Again, I want totals
for any part of the date range, not just across the whole range.

 

[Dallman Ross]

Starting with this approach:

<<"An alternative I wouldn't mind, in case it's easier, is:
on each buy date, what's my total current position?">>

Col B - Quantity Purch.
Col C - Date Purch.
Col D - Current Position
Col E - Quantity Sold
Col F - Date Sold

Data starts Row 4

Leave D3 EMPTY
Enter this in D4:

=IF(C4>0,SUM($B$4:B4)-SUMPRODUCT(($F$4:F4<=C4)*($E$4:E4)),D3-E4)

Looks very interesting. Hard for me to visualize here,
for one, because the C's column always is greater than zero.
Hmm. I'll play with it; thanks!

 

[Guest]

(although I have yet to see that ampersand documented anywhere! Where do I find out more about such operators?

Search on "operators" in the Excel help files. You'll get a full list under "calculation operators in formulas.

Regards
Mark Graesser

 

[Dallman Ross]

In pertinent part in <[email protected]>, Dallman Ross
<dman@localhost.> spake thusly:

by $DEITY, but this is getting close:

=SUMIF(C$4:C$48,"<=" & F4,B$4:B$48)-SUMIF(C$4:C$48,"<" & C4,B$4:B$48)

And this, I think, is the answer!

=SUMIF(C$4:C$48,"<=" & F21,B$4:B$48)-SUMIF(F$4:F$48,"<" & C21,B$4:B$48)


<whew!>

Now that I have it, I think maybe I like the previous formula's results
better for what it tells me about my investments.

Now to see if I can get "how much do I own now?" at the time of each
new purchase, out of this genre of algorithm.

Thanks, very much for the ampersand insight. Still hoping to
hear where I can find out more about it and any similar operators.

Dallman

 

[Dallman Ross]

Search on "operators" in the Excel help files. You'll get a full list
under "calculation operators in formulas."

Thanks, Mark. I'll take a closer look; though I did try that last
night, or so I thought! Maybe I missed it.

 

[RagDyer]

<<"Hard for me to visualize here, for one, because the C's column always is
greater than zero">>

This was meant for you to try a new, blank sheet, from scratch, and see if
the formula does anything close to what you can use.
You copy the formula down Col D before entering your test data.
Then follow what is displayed in Col D as you enter various buy and sell
test transactions.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Starting with this approach:

<<"An alternative I wouldn't mind, in case it's easier, is:
on each buy date, what's my total current position?">>

Col B - Quantity Purch.
Col C - Date Purch.
Col D - Current Position
Col E - Quantity Sold
Col F - Date Sold

Data starts Row 4

Leave D3 EMPTY
Enter this in D4:

=IF(C4>0,SUM($B$4:B4)-SUMPRODUCT(($F$4:F4<=C4)*($E$4:E4)),D3-E4)

Looks very interesting. Hard for me to visualize here,
for one, because the C's column always is greater than zero.
Hmm. I'll play with it; thanks!

 

[Dallman Ross]

<<"Hard for me to visualize here, for one, because the C's column
always is greater than zero">>

This was meant for you to try a new, blank sheet, from scratch, and
see if the formula does anything close to what you can use. You copy
the formula down Col D before entering your test data. Then follow
what is displayed in Col D as you enter various buy and sell test
transactions.

Okay. Thanks! I'll try it out tomorrow and see how it works.
Much obliged!

Dallman

 

[Dallman Ross]

This was meant for you to try a new, blank sheet, from scratch, and
see if the formula does anything close to what you can use.

I finally had some time to try this. Sorry, RagDyer, nice try, but
no dice. Your formula results in the following under "Position":

COL B COL C COL D COL E COL F COL O
Quantity Date Acquired Price Paid Adjusted Cost Date Sold Position

500 14-Jun-01 7.00 3,500.00 13-Jul-01 500
500 14-Jun-01 6.79 3,395.00 13-Jul-01 1000
500 16-Jul-01 6.95 3,475.00 19-Jul-01 -5395
500 17-Jul-01 6.58 3,290.00 20-Jul-01 -4895
500 24-Jul-01 6.76 3,380.00 7-Aug-01 -11160
500 24-Jul-01 6.92 3,460.00 7-Aug-01 -10660
1000 1-Aug-01 6.12 6,120.00 22-Aug-01 -9660

I did figure out how to get the open position as of the purchase
date, however. It is very close to the formula I used to
give me the max position held at any time during the current
trade's date spread.

Max position held during date range:
=SUMIF($C:$C,"<=" & $F5,$B:$B)-SUMIF($F:$F,"<=" & $C5,$B:$B)

Open position at time of purchase (this is the new formula):
=SUMIF($C:$C,"<="&$C5,$B:$B)-(SUMIF($F:$F,"<"&$C5,$B:$B))

Here is the calculated open position for the above excerpt of data:

COL N COL O
Max Position Open Position
in Dt. Range at Buy Date
1000 1000
1000 1000
1000 500
1000 1000
2000 1000
2000 1000
4000 2000

 

[RagDyer]

I'm glad you've solved your problem.

However, plugging your data into the suggested formula I posted, yielded
*nothing* close to what you posted here.

I would assume that when you switched your columns from my scenario to your
scenario, you made some sort of error.

But that's a mute point now, since you have resolved the predicament to your
satisfaction.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

This was meant for you to try a new, blank sheet, from scratch, and
see if the formula does anything close to what you can use.

I finally had some time to try this. Sorry, RagDyer, nice try, but
no dice. Your formula results in the following under "Position":

COL B COL C COL D COL E COL F COL O
Quantity Date Acquired Price Paid Adjusted Cost Date Sold Position

500 14-Jun-01 7.00 3,500.00 13-Jul-01 500
500 14-Jun-01 6.79 3,395.00 13-Jul-01 1000
500 16-Jul-01 6.95 3,475.00 19-Jul-01 -5395
500 17-Jul-01 6.58 3,290.00 20-Jul-01 -4895
500 24-Jul-01 6.76 3,380.00 7-Aug-01 -11160
500 24-Jul-01 6.92 3,460.00 7-Aug-01 -10660
1000 1-Aug-01 6.12 6,120.00 22-Aug-01 -9660

I did figure out how to get the open position as of the purchase
date, however. It is very close to the formula I used to
give me the max position held at any time during the current
trade's date spread.

Max position held during date range:
=SUMIF($C:$C,"<=" & $F5,$B:$B)-SUMIF($F:$F,"<=" & $C5,$B:$B)

Open position at time of purchase (this is the new formula):
=SUMIF($C:$C,"<="&$C5,$B:$B)-(SUMIF($F:$F,"<"&$C5,$B:$B))

Here is the calculated open position for the above excerpt of data:

COL N COL O
Max Position Open Position
in Dt. Range at Buy Date
1000 1000
1000 1000
1000 500
1000 1000
2000 1000
2000 1000
4000 2000

 

[Dallman Ross]

I'm glad you've solved your problem.

However, plugging your data into the suggested formula I posted,
yielded *nothing* close to what you posted here.

Rag,

Playing with what you suggested some more, I did get it to work
as advertised. Thank you! Interesting approach.

What I'd done wrong was that I had other data in Col. E. I
re-read your original message and saw that I'd neglected
that part. (Each line of my data is actually a round-turn
sale of the amount in Col. B. So Col. E isn't required.)

Here is your formula adjusted to work with the data as I
have it:

=IF(C4>0,SUM($B$4:B4)-SUMPRODUCT(($F$4:F4<=C4)*($B$4:B4)),D3-B4)

You might even have a simpler expression to offer given the
information I've provided about Col. B.

The one caveat to this approach is that the data needs to
be date-sorted, I believe. It is, anyway, in my worksheet.
But I thought that was worth mentioning for those following
along vicariously.

Dallman

 

[Dallman Ross]

Here is your formula adjusted to work with the data as I have it:

=IF(C4>0,SUM($B$4:B4)-SUMPRODUCT(($F$4:F4<=C4)*($B$4:B4)),D3-B4)

Pardon the follow-up on myself again, but I've been looking
trying to understand how this works. I realize that the IF
statement is there because of your taking into consideration
that the quantity sold could differ. Since it doesn't in
my data, this simplified version gives the same result:

=SUM($B$3:B9)-SUMPRODUCT(($F$3:F9<=C9)*($B$3:B9))

Btw, it doesn't change the outcome if the data was or
wasn't there to start with when I put the formula in place.
I.e., formula first, then data, or data first, then formula;
same result.

Thanks again.

 

[Ragdyer]

When I configured the formula and the test form, I envisioned a real time,
ongoing ledger type list where one could keep track of transactions and
their current standings (positions).
And yes, you're correct in stating that I constructed the formula to take
partial sales into consideration.
And yes again, since this is supposed to be "ongoing", the dates would
automatically be chronological (sorted), since they would be entered as each
transaction transpired.

I neglected to take into consideration the possibility of the formula being
"plugged into" an existing sheet of historical data.
I should have noticed that by the dates of some of your examples.

Anyway, I'm glad that you've been helped by your use of this NG.