|
Notes on Combining the Signals recorded in web Query workbook with
Historical Prices from Yahoo.
<To Page 1
To Page 3>
- Make a query to combine all TopBot
Signals with the Stock prices for the next 21 days.
- Query H1A1qry
- Make New Query.
Add Tables: TopBot and StockPrices.
Join Symbol and TickOut.
Any Symbol in TopBot may appear on one day (DateofOpinion). The
symbol in StockPrices will appear for many historical days (DatePrice).
By joining them we will be able to see a signal and the prices after the
signal.
 -
- Calculated Fields:
- Days: DatePrice-DateOfOpinion
- PriceChg: StockPrice.Close - TopBot.Last
- PriceChgRatio: PriceChg/(TopBot.last)
SELECT TopBot.TableName, TopBot.DateOfOpinion, TopBot.Symbol,
TopBot.[Company Name], TopBot.Last, StockPrices.DatePrice, StockPrices.Close,
[dateprice]-[dateofopinion] AS Days, [Close]-[last] AS PriceChg, [pricechg]/[last]
AS PriceChgRatio, TopBot.[Today's], TopBot.[Yesterday's], TopBot.[Last
Week's]
FROM TopBot INNER JOIN StockPrices ON TopBot.Symbol = StockPrices.TickerOut;
- Tip: You can replace the name "TopBot" or "StockPrices"
with a shorter word or letter called an Alias.
- Click on the Table in the upper part of the Query Designer. In the
Field List Properties box, enter the short name in the Alias box.
- Apply Aliases (TopBot AS T, StockPrices AS P)
SELECT T.TableName, T.DateOfOpinion, T.Symbol, T.[Company
Name], T.Last, P.DatePrice, P.Close, [dateprice]-[dateofopinion] AS Days,
[Close]-[last] AS PriceChg, [pricechg]/[last] AS PriceChgRatio, T.[Today's],
T.[Yesterday's], T.[Last Week's]
FROM TopBot AS T INNER JOIN StockPrices AS P ON T.Symbol =
P.TickerOut;-
- Show all prices after the signal up to 21 days (3 weeks) later.
SELECT T.TableName, T.DateOfOpinion, T.Symbol, T.[Company Name], T.Last,
P.DatePrice, P.Close, [dateprice]-[dateofopinion] AS Days, [Close]-[last] AS
PriceChg, [pricechg]/[last] AS PriceChgRatio, T.[Today's], T.[Yesterday's],
T.[Last Week's]
FROM TopBot AS T INNER JOIN StockPrices AS P ON T.Symbol = P.TickerOut
WHERE ((([dateprice]-[dateofopinion])>=0 And ([dateprice]-[dateofopinion])<=21));
- To reduce the number of decimals displayed in the query, Click on the
Field for PriceChg. In the Properties box, choose Format: Fixed,
Decimal Places:2.
Repeat for PriceChgRatio as Format: Percent, Decimal Places 2.
-
- Sort by T.Symbol, T.Last, T.DatePrice.
There is a possibility that we have loaded the same signal more than once.
To eliminate duplicates, click on the upper area of the query designer.
In Query Properties, set Unique Values: Yes.
 -
So in this example, a Bottom 100 signal of AAA on 7/23/2004, was 2.4% lower
in 5 days (3 trading days). Seems like a good signal.
How about others?
Save the Query as H1A1qrySignalPrices-
- Bring them into an Excel Pivot table.
To Page 3>
|
