Notes on Combining the Signals recorded in web Query workbook with
Historical Prices from Yahoo. Page 3: Analyze the Data in an
Excel Pivot Table and Pivot Chart.
<To Page 2
- Make a Pivot Table to read the Query
of SignalPrices.
- Open a new Excel workbook.
Menu: Data >> Pivot Table and Pivot Chart Report
Choose: External Data. Choose: Pivot Chart Report (with Pivot
Table report) Next >
Click the Get Data.. button
Databases Tab. Choose MS Access Database.
Click OK (not Browse).
- Locate the StockSignals.mdb. Click Open
- Choose a data table:
Choose the H1A1qry... , then click the ">" button to get all the
columns.
(or expand the table, and select columns with the ">" button.)
Click. Next >
On the Query Wizard - Filter Data dialog, choose nothing and click Next >
Select nothing for Sort Order. Click Next >
Select "Return Data to Microsoft Excel" Click
Finish
Set Existing Worksheet. Select a location for the upper left
cell of the pivot table. Click Finish.
- Choose the Pivot Fields to
display on the Pivot Chart by dragging data into appropriate dimension.
Drag Fields from the Pivot Table Field List directly to the areas on the
Plot.
Start with PriceChgRatio onto the Data Items.-
- Switch the grouping of data
from Count to Average
- Most Data defaults to Count. To switch to Average:
- Right click the "Count of PriceChgRatio" field Button.
In the Summaries by: list box, Switch Count to Average. Click OK
Drag TableName to the "Drop Series Fields Here" Box
The default colors cannot be easily changed and kept.-
- Excel Bug: You cannot change the
Pivot Chart Data series colors and have them stay.
- This says it is for XL 2000, but the same problem seems to be there for
XL2003.
- 215904 -
XL2000 Changing a PivotChart Removes Series Formatting
http://www.mrexcel.com/archive/Chart/22456.html (Mr. Excel
tip doesn't work for me.)
Workaround: Record a macro to redo the formatting.
(maybe some other SIG night....)
- Continue dragging Pivot
Fields onto the Pivot Chart
- Drag Days ont the X axis (Drop Category Fields Here) box.
Drag DateOfOpinion to the "Drop Page Fields Here" box.
Here we go.... But we don't want them stacked.
- Change to a Side by
Side Column Chart
- Right Click on the chart. Choose Chart Type...
Switch from the Stacked Column to the Side by Side Columns.
Click OK.
-
- Apply Chart Titles.
- Menu: Chart >> Chart Options..
- Add text for Chart Title, X-Axis Title, Y-Axis Title
-
- This is a useful chart.
But an unexpected outcome.
- Wait a minute! Blue is the Bottom 100. Red is
the Top 100.
The Bottom 100 are up more than the Top100!
- We have not included any normalization of the gains and losses with
respect to the general market through the QQQ. But what is
unmistakable is the average of the Bottom 100 (the ones to sell) do better
than the Top 100(the ones to buy).
At best this is a contrarian signal.
|
