Notes on Combining the Signals recorded in web Query workbook with
Historical Prices from Yahoo.
- Summary
Next Page>
- 1. Get list of Signals recorded in webQuery5.
2. Run the Yahoo Historical
3. New Access Database "StockSignals.mdb"
- 4. Import the Database and the DBUpG tables from WebQuery5.xls
- 5. Import table HPDB From YahooHistorical.xls
- 6. Edit the design of the Access tables to convert text numbers or
dates as necessary.
Get List of Signals recorded in webQuery5.- Create a Criteria range with "Symbol" "<>" in N1:N2
Create a Extract range with "Symbol" at L1.
 Data >> Filter >> Advanced
Filter >>
Copy to another Location
The List Range is the Column range A:I.- Criteria: is N1:N2, any non-blank symbol
- Create the list starting a L1.
- We only want one entry for each symbol, so
- check the Unique records Only.
- OK.
-
Repeat the process with the DBUpG sheet.
- Finally, combine the two lists from Database Symbols and DBUpG Symbols.
Do another Advanced Filter to get the unique records.
Copy the list of unique symbols to the YahooHistorical8a.xls workbook, WebQ
worksheet in the Hopper Range.
Paste Special Values at A6.
Place QQQ in A5 to get a measure of the overall market
- Run the YahooHistorical.
- Set the Hopper Range: Insert >> Names >> Define.
Set Hopper to WebQ!$A$5:$A$2100.
- Make sure the HPDB is empty. Only the headers should remain.
On the WebQ sheet, Enter the Year, Month number, and Day numbers for From
and To dates of the historical prices. Enter this in the orange
FromTo range.
Press the H05 Run Hopper button.
The macro can do one Ticker per second. Successfully processed
tickers have a + in front of the symbol in the hopper.
- While YahooHistorical is running, open Access.
Import the Excel tables into an Access
Database for easy querying.
- Make sure WebQuery5.xls is saved and closed.
New Database: StockSignals.mdb.
File >> Get External Data >> Import... File Types, Choose Excel.
Choose the WebQuery5.xls file.
On the Import Spreadsheet Wizard, Select "show worksheets".
Choose "Database" (the database worksheet). Next >
Check "First Row contains Column Headers" Next >
- Choose "In a New Table". Next >
- There is bug on the next screen. You cannot change the field
options. Is this an Access 2003 bug? Next >
- No Primary Key. (The primary key will be the Symbol and
Date. we cannot specify a compound key here)
- Import to Table "TopBot". Finish >
A few thousand rows will be read into the access table in under 20 seconds.
Repeat the process for the DBUpG sheet. Write to a new
sheet: UpDownGrade
After the YahooHistorical run is finished, save and close the workbook.- From Access, Import the HPDB spreadsheet. Same proceedure as
with WebQuery. Save to Table "StockPrices"
-
- Change the Design of
the Access Tables
- From Access, DatabaseWindow, Select the "StockPrices" table, and press
the Design toolbar button.
- TickerOut must be text, Date must be a DateTime and change the name to
DatePrice.
Open, High, Low, Close, Volume, AdjClose all need to be Number type Single
Save and Close the Table. - The records that show a payment of dividend will be deleted.
They are in the Open data field and cannot be stored in a text
Perform the same changes to the TopBot and UpDownGrade tables.
-
Next Page>
|
