Pivot Table SQL and Calc Fields

[farsider]

I'm using Microsoft Query data to a Pivot Table with the following SQL statement:

SELECT datalist.symbol, datalist.co_name, datalist.date, datalist.action, datalist.stock, datalist.price, datalist.amount, datalist.fee, datalist.total_share, datalist.avg_cost,
 
[COLOR=Blue]iif(ACTION='SELL',-1,1)*stock AS 'netstock'[/COLOR]

FROM `C:\My Documents\atestdata`.datalist datalist

The NetStock converts the stock field to a negative for and items that are sold. This is so I can get the net shares for each symbol.

Sheet1

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 74px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</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></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="font-weight: bold;">symbol</td><td style="font-weight: bold;">co_name</td><td style="font-weight: bold;">date</td><td style="font-weight: bold;">action</td><td style="font-weight: bold;">stock</td><td style="font-weight: bold;">price</td><td style="font-weight: bold;">amount</td><td style="font-weight: bold;">fee</td><td style="font-weight: bold;">total_share</td><td style="font-weight: bold;">avg_cost</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>StockA </td><td>Company A</td><td style="text-align: right;">9/30/2010</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">100</td><td style="text-align: right;">0</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>StockA </td><td>Company A</td><td style="text-align: right;">8/31/2010</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">100</td><td style="text-align: right;">0</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>StockA </td><td>Company A</td><td style="text-align: right;">7/30/2010</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">100</td><td style="text-align: right;">0</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td>StockA </td><td>Company A</td><td style="text-align: right;">6/30/2010</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">100</td><td style="text-align: right;">0</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td>StockA </td><td>Company A</td><td style="text-align: right;">5/31/2010</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">100</td><td style="text-align: right;">0</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td>StockB </td><td>Company B</td><td style="text-align: right;">5/14/2010</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">120</td><td style="text-align: right;">0</td><td style="text-align: right;">0</td><td style="text-align: right;">5.21</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td>StockA </td><td>Company A</td><td style="text-align: right;">4/30/2010</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">100</td><td style="text-align: right;">0</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td>StockB </td><td>Company B</td><td style="text-align: right;">4/20/2010</td><td>SELL </td><td style="text-align: right;">2,000.00</td><td style="text-align: right;">7.85</td><td style="text-align: right;">15,690.01</td><td style="text-align: right;">9.99</td><td style="text-align: right;">0</td><td style="text-align: right;">5.21</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td>StockB </td><td>Company B</td><td style="text-align: right;">4/15/2010</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">120</td><td style="text-align: right;">0</td><td style="text-align: right;">2,000.00</td><td style="text-align: right;">5.21</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>StockA </td><td>Company A</td><td style="text-align: right;">3/31/2010</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">100</td><td style="text-align: right;">0</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td>StockB </td><td>Company B</td><td style="text-align: right;">3/15/2010</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">120</td><td style="text-align: right;">0</td><td style="text-align: right;">2,000.00</td><td style="text-align: right;">5.21</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td>StockA </td><td>Company A</td><td style="text-align: right;">2/26/2010</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">100</td><td style="text-align: right;">0</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td>StockB </td><td>Company B</td><td style="text-align: right;">2/12/2010</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">120</td><td style="text-align: right;">0</td><td style="text-align: right;">2,000.00</td><td style="text-align: right;">5.21</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td>StockA </td><td>Company A</td><td style="text-align: right;">1/29/2010</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">100</td><td style="text-align: right;">0</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td>StockB </td><td>Company B</td><td style="text-align: right;">1/15/2010</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">120</td><td style="text-align: right;">0</td><td style="text-align: right;">2,000.00</td><td style="text-align: right;">5.21</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td>StockA </td><td>Company A</td><td style="text-align: right;">12/31/2009</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">100</td><td style="text-align: right;">0</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td><td>StockB </td><td>Company B</td><td style="text-align: right;">12/15/2009</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">120</td><td style="text-align: right;">0</td><td style="text-align: right;">2,000.00</td><td style="text-align: right;">5.21</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td><td>StockA </td><td>Company A</td><td style="text-align: right;">11/30/2009</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">100</td><td style="text-align: right;">0</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td><td>StockB </td><td>Company B</td><td style="text-align: right;">11/13/2009</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">120</td><td style="text-align: right;">0</td><td style="text-align: right;">2,000.00</td><td style="text-align: right;">5.21</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td><td>StockA </td><td>Company A</td><td style="text-align: right;">10/30/2009</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">100</td><td style="text-align: right;">0</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">22</td><td>StockB </td><td>Company B</td><td style="text-align: right;">10/15/2009</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">120</td><td style="text-align: right;">0</td><td style="text-align: right;">2,000.00</td><td style="text-align: right;">5.21</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">23</td><td>StockA </td><td>Company A</td><td style="text-align: right;">9/30/2009</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">100</td><td style="text-align: right;">0</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">24</td><td>StockB </td><td>Company B</td><td style="text-align: right;">9/15/2009</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">120</td><td style="text-align: right;">0</td><td style="text-align: right;">2,000.00</td><td style="text-align: right;">5.21</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">25</td><td>StockA </td><td>Company A</td><td style="text-align: right;">8/31/2009</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">100</td><td style="text-align: right;">0</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">26</td><td>StockB </td><td>Company B</td><td style="text-align: right;">8/14/2009</td><td>DIVIDEND </td><td style="text-align: right;">0</td><td>- </td><td style="text-align: right;">120</td><td style="text-align: right;">0</td><td style="text-align: right;">2,000.00</td><td style="text-align: right;">5.21</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">27</td><td>StockA </td><td>Company A</td><td style="text-align: right;">7/8/2009</td><td>BUY </td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.97</td><td style="text-align: right;">6,979.99</td><td style="text-align: right;">9.99</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">6.98</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">28</td><td>StockB </td><td>Company B</td><td style="text-align: right;">7/7/2009</td><td>BUY </td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">5.01</td><td style="text-align: right;">5,019.99</td><td style="text-align: right;">9.99</td><td style="text-align: right;">2,000.00</td><td style="text-align: right;">5.21</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">29</td><td>StockB </td><td>Company B</td><td style="text-align: right;">7/3/2009</td><td>BUY </td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">5.39</td><td style="text-align: right;">5,399.99</td><td style="text-align: right;">9.99</td><td style="text-align: right;">1,000.00</td><td style="text-align: right;">5.4</td></tr></tbody></table>
When I run the Pivot Table the Data total is of the calculated field created above - 'NetStock':

Sheet1

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 74px;"><col style="width: 76px;"><col style="width: 35px;"><col style="width: 39px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>Sum of netstock</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>symbol</td><td>co_name</td><td>Total</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td>StockA </td><td>Company A</td><td style="text-align: right;">1000</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td>StockB </td><td>Company B</td><td style="text-align: right;">0</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td>Grand Total</td><td>
</td><td style="text-align: right;">1000</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>

The totals are correct, however I'd like to not show any line that totals 0 (zero) and I'm wondering if this could be done somehow in the Pivot Table and/or in the SQL statement?

Thanks

 

[Fazza]

Maybe not what you want, but simple to implement is to autofilter the pivot table. Per your pivot table sample, select cell D4 (so one cell right of the pivot table) and also C4 then apply an autofilter (ALT-D-D-F). Now it is simple to filter for totals other than 0.

Within the SQL, it depends exactly on what you want. To exclude sums of zero from the dataset, one way is like this,

SELECT field1, field2, field3, SUM(field to sum) AS [New Name]
FROM table
GROUP BY field1, field2, field3
HAVING SUM(field to sum) <> 0

Hence, for your field names, this might be OK.

SELECT d.symbol, d.co_name, d.date, d.action, d.stock, d.price, d.amount, d.fee, d.total_share, d.avg_cost, IIF(d.ACTION='SELL',-1,1)*d.stock AS [netstock]
FROM datalist d
GROUP BY d.symbol, d.co_name, d.date, d.action, d.stock, d.price, d.amount, d.fee, d.total_share, d.avg_cost
HAVING ABS(SUM(IIF(d.ACTION='SELL',-1,1)*d.stock))>.0001

You'll see I've changed the test to ABS > .0001 to overcome any rounding problems.

HTH

 

[farsider]

Fazza - many thanks for the SQL statement and at first I got the same results in the Pivot Table, so I read your comments on the autofilter. That's genius!

That tip alone made my day and will make the Pivot Table option even more powerful.

Just wondering if you could recommend a good source for using SQL with Excel. Or do I just need to know generic SQL?

 

[Fazza]

Generic SQL covers what you need. Excel has its particular syntax but there isn't too much to it. Things like

SELECT fields FROM [sheetname$]

SELECT fieldnamewithoutspace, `field name with space`

SELECT field_name AS [New Name]

for difficult characters, maybe
SELECT `%$&#`

for difficult names, maybe
SELECT [Group]

Watch out for names. So field names such as Group & Date

This thread has some good links http://www.mrexcel.com/forum/showthread.php?t=315768

BTW, I rarely use entire worksheets as the data source, prefering to use defined named ranges. Dynamic named ranges are not suitable.

A book that covered SQL in Excel from a few years ago is by Timothy Zapawa. There was a second edition that I haven't seen. For a relative newcomer it should be great; if you already know a bit there may not be too much new material covered. Still, it is relatively inexpensive & so worth grabbing a copy.

SQL is easy & simple to learn - very worthwhile, I've found. I've learnt it only for use in Excel 'cause that is where I do 99.9% of my work.

Once you're comfortable with the approach, a great next step is some ADO (& recordsets).

regards

 

[Fazza]

PS.

And of course, Google is always a tremendous resource. Don't forget Microsoft's knowledge base info too - it covers all sorts of details.

F

 

[farsider]

Thanks again - I'm ready to jump in!

I've done some further testing on the earlier data and I'm maybe getting a little confused on what SQL select. In my earlier note I tried your example and it still seems to show a record with the sum (field) as zero.

So the only way I was able to only extract the records where the sum is 0 was with this code:

SELECT datalist.symbol, Sum(datalist.stock_tot) AS 'sumtotal'
FROM `C:\My Documents\atestdata`.datalist datalist
GROUP BY datalist.symbol
HAVING (Sum(datalist.stock_tot)<>0)

This returns:
Sheet1

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 54px;"><col style="width: 67px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="font-weight: bold;">symbol</td><td style="font-weight: bold;">'sumtotal'</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>StockA </td><td style="text-align: right;">1000</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>
</td><td>
</td></tr></tbody></table>
If I include anything else in Select:

SELECT datalist.symbol, Sum(datalist.stock_tot) AS 'sumtotal', [COLOR=Blue]datalist.action[/COLOR]
FROM `C:\My Documents\atestdata`.datalist datalist
GROUP BY datalist.symbol, [COLOR=Blue]datalist.action[/COLOR]
HAVING (Sum(datalist.stock_tot)<>0)

I get the following:

Sheet1

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 54px;"><col style="width: 67px;"><col style="width: 46px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="font-weight: bold;">symbol</td><td style="font-weight: bold;">'sumtotal'</td><td style="font-weight: bold;">action</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>StockA </td><td style="text-align: right;">1000</td><td>BUY </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>StockB </td><td style="text-align: right;">2000</td><td>BUY </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>StockB </td><td style="text-align: right;">-2000</td><td>SELL </td></tr></tbody></table>
So even though the sum of symbol "StockB" is 0 I get the other records. Maybe this is working as it should? I can't seem to Group without adding the fields. Doing this at the moment!

Thanks again for your help.