join two tables to get tomorrow's price (and the price two days from "now")

Question

I'm trying to do a JOIN query to analyze some stocks. In my first table called top10perday, I list 10 stocks per day that I have chosen to "buy" the next day and sell the following day:

date        symbol
07-Aug-08   PM
07-Aug-08   HNZ
07-Aug-08   KFT
07-Aug-08   MET
...
08-Aug-08   WYE
08-Aug-08   XOM
08-Aug-08   SGP
08-Aug-08   JNJ

For instance, for record #1:

• the date of the record is 07-Aug-08
• I want to buy a share of PM stock on the next trading day after 07-Aug-08 (which is 08-Aug-08)
• I want to sell that shar eof PM stock two trading days after 07-Aug-08), which turns out to be 11-Aug-08

My stock prices are in a table called prices, which looks like this:

date        symbol   price
07-Aug-08   PM       54.64
08-Aug-08   PM       55.21
11-Aug-08   PM       55.75
12-Aug-08   PM       55.95
... many more records with trading day, symbol, price

I want to do a JOIN so that my result set looks like this:

date        symbol  price-next-day  price-two-days 
07-Aug-08   PM      55.21           55.75   
...
list one record per date and symbol in table1.

I have tried doing something like:

SELECT top10perday.date, top10perday.symbol, Min(prices.date) AS MinOfdate
FROM prices INNER JOIN top10perday ON prices.symbol = top10perday.symbol
GROUP BY top10perday.date, top10perday.symbol
HAVING (((Min(prices.date))>[date]));

I have tried many variations of this, but I'm clearly not on the right path, because the result set just includes 10 rows as of the earliest date shown in my top10perday table.

I am using Microsoft Access. Thanks in advance for your help! :-)

Answer 1

I'm not a guru on this transformation but I can point you at an idea. Try using Pivot on the date column for each symbol in your query from a date to a date. This should give you a table with many columns with the name of the date you're using, and the price on each day. Indeed it should do this for every stock symbol you have over a given time.

Based on what you're trying to graph though, I think it would be interesting for you to look at the VWSP not just the spot price on your trades if you're trying to plot the stock performance.

Answer 2

My guess is:

SELECT top10perday.date, top10perday.symbol, MIN(pnd.price) AS PriceNextDay, MIN(ptd.price) AS PriceTwoDays 
FROM top10perday 
LEFT OUTER JOIN prices AS pnd ON (pnd.symbol = top10perday.symbol AND pnd.date > top10perday.date) 
LEFT OUTER JOIN prices AS ptd ON (ptd.symbol = top10perday.symbol AND ptd.date > pnd.date) 
GROUP BY top10perday.date, top10perday.symbol
HAVING ((pnd.date = Min(pnd.date) AND ptd.date = Min(ptd.date));

It´s just a shoot in the dark but my reasoning is: List all stocks you want (top10perday) and for each stock get the price, if exists, with mininum date after its date to populate the PriceNextDay and the price with minimun date after the PriceNextDay to populate the PriceTwoDays. The performance may stinks. But test it and see if it works. Later we can try to improve it.

EDITed to include Rob Farley´s comment.

Answer 3

This should just be a join between three copies of the prices table. The problem is that you need to join to the next trading day, and that's a slightly trickier problem, since it's not always the next day. So we end up with a more complex situation (particularly as some days are skipped beacuse of holidays).

If it weren't Access you could use row_number() to order your prices by date (using a different sequence per stock code).

WITH OrderedPrices AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY date) AS RowNum
FROM Prices
)
SELECT orig.*, next_day.price, two_days.price
FROM OrderedPrices orig
  JOIN
   OrderedPrices next_day
   ON next_day.symbol = orig.symbol AND next_day.RowNum = orig.RowNum + 1
   JOIN
   OrderedPrices two_days
   ON two_days.symbol = orig.symbol AND two_days.RowNum = orig.RowNum + 2
;

But you're using Access, so I don't think you have ROW_NUMBER().

Instead, you could have a table which lists the dates, having a TradingDayNumber... then use that to facilitate your join.

SELECT orig.*, next_day.price, two_days.price
FROM Prices orig
  JOIN
  TradingDays d0
   ON d1.date = orig.date
  JOIN
  TradingDays d1
   ON d1.TradingDayNum = d0.TradingDayNum + 1
  JOIN
  TradingDays d2
   ON d2.TradingDayNum = d0.TradingDayNum + 2
  JOIN
   Prices next_day
   ON next_day.symbol = orig.symbol AND next_day.date = d1.date
   JOIN
   Prices two_days
   ON two_days.symbol = orig.symbol AND two_days.date = d2.date

But obviously you'll need to construct your TradingDays table...

Rob

Answer 4

I used an intermediate query, Query1:

SELECT
    t.symbol
    , t.qdate
    , (SELECT TOP 1 p1.qdate FROM Prices AS p1 
        WHERE p1.symbol = t.symbol
        AND p1.qdate > t.qdate
        ORDER BY p1.qdate ) AS next_day
FROM Top10perday AS t;

Then Query1 was one of the sources in Query2:

SELECT
    t.symbol
    , t.qdate
    , p2.price AS price_next_day
    , (SELECT TOP 1 p1.price FROM Prices AS p1
        WHERE p1.symbol = t.symbol
        AND p1.qdate > t.next_day
        ORDER BY p1.qdate ) AS price_two_days
FROM Query1 AS t
    LEFT JOIN Prices AS p2
    ON (t.next_day = p2.qdate)
    AND (t.symbol = p2.symbol);

I sure won't claim that's great SQL. And it may run slow as a bloated pig (appropriate indexing should help). But it works, and I can understand it.

Answer 5

This syntax worked in Access 2003:

SELECT t10.Date, t10.Symbol, p1.date, p1.price, p2.date, p2.price FROM (top10perday AS t10 LEFT JOIN prices AS p1 ON t10.Symbol = p1.symbol) INNER JOIN prices AS p2 ON t10.Symbol = p2.symbol WHERE ( ((p1.date)=((Select Min([date]) as md from prices where [date]>t10.[Date] and symbol = t10.symbol )) ) AND ((p2.date)=((Select Min([date]) as md from prices where [date]>p1.[Date] and symbol = t10.symbol) )) );

the idea is to get the first (min) date that is greater than the date in the previous table (top10perday and the prices as p1)