Returning Currently-Held Securities from a Transaction Ledger

[JYTS]

Hi,

I have a list of transactions, with dates, ticker symbols, share counts, prices, etc. that represent trading in a certain portfolio. The transactions are not all 100% buys or sells, sometimes a fraction of a certain stock is sold. I would like to return a list of currently-held securities as-of a certain date, along with the number of shares held.

So for example, I would like to turn

Date	Ticker	Action	Shares
1/5/15	XOM	Buy	200
1/5/15	PCP	Buy	150
1/6/15	CVX	Buy	100
2/5/15	PCP	Sell	90
3/23/15	CVX	Buy	100
4/6/15	JNJ	Buy	45
4/22/15	JNJ	Sell	45

into a neat table of...

Ticker	Shares
XOM	200
PCP	60
CVX	200

(at time 8/1/15)

Is this do-able, and if so how?

Thanks!

[FDibbins]

Try this...

	A	B	C	D
1	Date	Ticker	Action	Shares
2	1/5/2015	XOM	Buy	200
3	1/5/2015	PCP	Buy	150
4	1/6/2015	CVX	Buy	100
5	2/5/2015	PCP	Sell	90
6	3/23/2015	CVX	Buy	100
7	4/6/2015	JNJ	Buy	45
8	4/22/2015	JNJ	Sell	45
9
10
11	XOM	200
12	PCP	60
13	CVX	200

B11=SUMIFS($D$2:$D$8,$B$2:$B$8,A11,$C$2:$C$8,"Buy")-SUMIFS($D$2:$D$8,$B$2:$B$8,A11,$C$2:$C$8,"Sell")
copied down

[JYTS]

Hi FDibbins. Thanks for the reply.

I should add that the ticker of currently-held securities needs to be calculated too. It is far too many to do by hand. So in your example, A11:A13 would be calculated, dependent on an input date. Thanks!

[FDibbins]

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Include your input dates

[JYTS]

Attached is an example of what I'm looking for. The colors are just to make things easier to grasp--they're not on the real document. Thanks!

[cyiangou]

I think this works (needs 1 helper column):

transactions_ticker_cy.xlsx

[JYTS]

cylangou, that works perfectly. Thanks! Let me take a further look at it--I want to see if I can add additional filter criteria in--and then I'll mark as solved. Thanks

[JYTS]

How could I filter the results further? For example, the data includes a list that organizes each transaction into Consumer Staples, Industrials, Information Technology, etc. How can I filter so I only see Consumer Staples? The categories are just in a column.

[cyiangou]

This should do it:

transactions_ticker_cy2.xlsx

[JYTS]

That's perfect, thanks.

[cyiangou]

Cool, thanks.