Find last date running total equalled zero

[RandomTandem]

If I have a list of dates (in chronological order) in Column A and a list of stock purchases or sales in column B. Is there a formula which will help me find the last date where the running total was equal to zero prior to a date I put in cell C1?

In the below example, If I put 13th Aug in cell C1, it would tell me 12th Aug but if I had 15th Aug in cell C1, it would put 14th Aug

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Purchase/Sale Amount[/TD]
[/TR]
[TR]
[TD]10th Aug[/TD]
[TD]+5[/TD]
[/TR]
[TR]
[TD]11th Aug[/TD]
[TD]+10[/TD]
[/TR]
[TR]
[TD]12th Aug[/TD]
[TD]-15[/TD]
[/TR]
[TR]
[TD]13th Aug[/TD]
[TD]+80[/TD]
[/TR]
[TR]
[TD]14th Aug[/TD]
[TD]-80[/TD]
[/TR]
[TR]
[TD]15th Aug[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


I would normally use a helper column to achieve this but I cannot do it in this example.

Thank you.

 

[RandomTandem]

Are the values below A and B calculated by means of a formula? if so, care to post that formula?

No, they are hard coded. In fact, the values in columns A to C are all hard coded

By way of explanation, the spreadsheet is for a stock portfolio where I have a number of different brokerage accounts (these are the accounts A, B etc etc). If you are able (and thank you for all your help) to figure out the last time that the position was zero in any given account for any given date, it will help me work out my UK tax cost basis

Here's how it works:

Day 1: I buy 10 shares at a price of £10. My position is long 10 at an average price of £10
Day 2: I buy 10 shares at a price of £12. My position is long 20 at an average price of £11
Day 3: I sell 5 shares at a price of £15. My position is long 15 at an average price of £11 (my taxable profit is (£15-£11) per share on 5 share = £20. Note: my average buy price is not affected, it is still £11
Day 4: I sell the remaining 15 shares at £15, my tax able profit is £4 x 15 = £60.

As my position is now 0. The average price calculation resets

So, if on day 5,I buy 5 shares at £22, my cost basis is now long 5 at £22 i.e. none of the prices on days 1-4 matter.

So that's why I need to know at any point in time when was the last period that my position in a stock was 0 at each and every account.

Thanks again.

 

[Aladin Akyurek]

How about the values in column D and E? I had these in mind when posing the question.

 

[RandomTandem]

How about the values in column D and E? I had these in mind when posing the question.

Cell D2: =SUMIF($A$2:A2,"A",$C$2:C2)


Cell E2: =SUMIF($A$2:A2,"B",$C$2:C2)

and so on.

I only put those two columns into help illustrate the problem

(there are actually about 10 brokerage accounts as well)

 

[Aladin Akyurek]

Cell D2: =SUMIF($A$2:A2,"A",$C$2:C2)


Cell E2: =SUMIF($A$2:A2,"B",$C$2:C2)

and so on.

I only put those two columns into help illustrate the problem

(there are actually about 10 brokerage accounts as well)

Isthe following admissible?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr][tr][td]

1​

[/td][td]Account[/td][td]Date[/td][td]Buy/Sell[/td][td]A[/td][td]B[/td][td]C[/td][td][/td][td][/td][td]first date[/td][td][/td][td]last date[/td][/tr]
[tr][td]

2​

[/td][td]B[/td][td]

16-Jul-17​

[/td][td]

200​

[/td][td]-[/td][td]

200​

[/td][td]-[/td][td][/td][td]A[/td][td]

23-Jul-17​

[/td][td][/td][td]

23-Jul-17​

[/td][/tr]
[tr][td]

3​

[/td][td]A[/td][td]

17-Jul-17​

[/td][td]

50​

[/td][td]

50​

[/td][td]-[/td][td]-[/td][td][/td][td]B[/td][td]

19-Jul-17​

[/td][td][/td][td]

24-Jul-17​

[/td][/tr]
[tr][td]

4​

[/td][td]A[/td][td]

18-Jul-17​

[/td][td]

10​

[/td][td]

10​

[/td][td]-[/td][td]-[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]B[/td][td]

19-Jul-17​

[/td][td]

-200​

[/td][td]-[/td][td]

-200​

[/td][td]-[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]A[/td][td]

20-Jul-17​

[/td][td]

-40​

[/td][td]

-40​

[/td][td]-[/td][td]-[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]B[/td][td]

21-Jul-17​

[/td][td]

40​

[/td][td]-[/td][td]

40​

[/td][td]-[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]A[/td][td]

22-Jul-17​

[/td][td]

50​

[/td][td]

50​

[/td][td]-[/td][td]-[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]A[/td][td]

23-Jul-17​

[/td][td]

-70​

[/td][td]

-70​

[/td][td]-[/td][td]-[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]B[/td][td]

24-Jul-17​

[/td][td]

-40​

[/td][td]-[/td][td]

-40​

[/td][td]-[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In D2 just enter and copy across:

=IF($A2=D$1,SUMIFS($C2:C2,$A2:A2,$A2),"-")

In I2 control+shift+enter, not just enter, and copy down:

=INDEX($B$2:$B$10,MATCH(0,IF($A$2:$A$10=INDEX($D$1:$F$1,ROWS($I$2:I2)),SUBTOTAL(9,OFFSET(INDEX($D$2:$F$10,1,MATCH($H2,$D$1:$F$1,0)),0,0,ROW($B$2:$B$10)-ROW($B$2)+1)),"-"),0))

In K2 control+shift+enter, not just enter, and copy down:

=LOOKUP(9.99999999999999E+307,1/(IF($A$2:$A$10=INDEX($D$1:$F$1,ROWS($I$2:I2)),SUBTOTAL(109,OFFSET(INDEX($D$2:$F$10,1,MATCH($H2,$D$1:$F$1,0)),0,0,ROW($B$2:$B$10)-ROW($B$2)+1)),"-")=0),$B$2:$B$10)

 

[RandomTandem]

Yes - that is perfect. Thank you very much for your help on this.

 

[Aladin Akyurek]

Yes - that is perfect. Thank you very much for your help on this.

You are welcome.