Stock Trading Activity to Sch D format with Pivot Table

J

jbesr1230

Hello,
I have a stock trade database with the following fields.
1..Date
2..Action-(is Buy or Sell)
3..Quantity
4..Description
5..Unit Price
6..Total Amount
Buy amount is always negative and Sell amounts are positive

How would I set up a pivot table to get the following format by fields
(a schedule D format):
1..Description
2..Quantity
3..Action
4..Buy Date
5..Sell Date
6..Buy Amount
7..Sell Amount


For example-- Input data
Date...Act...Qty..Desc..UnitPx..TotAmt
Jan 1..Buy...3...TypeA..-10........-30
Jan 3 Sell...3...TypeA..17.........34

Desired output:
Desc:.Qty.Act..BDt..SDt..BAmt..SAmt..Net
TypeA..3..Buy..Jan1..xxx...-30...xxx
TypeA..3..Sell..xxx..Jan 3..xxx....34
Total..............................-30.....34.......4
the "Net" field in the output is calculated. "xxx" means no entry ie
blank.

Thanks

JB
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

problem with pivot table 3
Need advice for Pivot table 2
Advice needed for Pivot table 1
Mutual Fund Problem 2
Need help with query strategy 5
DTS: SQLServer to Excel in unattended server environment 0
Ok, so close, just need a little push to get form inputs into repo 5
If statement with Vlookup - including ISNA function to compare two columns from different worksheets 4

Top