SQL - Check for price changes over multiple periods

Question

I am using an MS Access database and am trying to make a query that provides an overview of securities for which the price changed by more than XX% during the last XY consecutive months. I have tried all kind of subqueries but cannot get my head around this.

Please find below a simplified example. The PriceTable contains three attributes: a period, a security id and the price of the security in that period. I am looking for a query that provides me per the last period (in this case 201210) all securities having a price change of more than plus or minus XX% (in this case 3%) in the last XY (in this case 3) months. The three columns on the right hand provide some calculations to further clarify this:

• Delta is the price change from one period to the other ((PT-PT-1)/PT-1)

• Delta>Threshold: checks whether the change is larger than (plus or minus) 3% (parameter XX)

• Counter: checks whether the price change is larger than 3% for 3 (parameter XY) consecutive months In the example below the query should only show productID number 1.

---

PriceTable Supporting calculations

+--------+------+-------+--------+-----------------+---------+
+ Period |SecID | Price | Delta% | Delta>Threshold | Counter |
+--------+------+-------+--------+-----------------+---------+
| 201206 |    1 |   105 |     0% |               N |       0 |
| 201207 |    1 |   100 | -4.76% |               Y |       1 |
| 201208 |    1 |    95 |    -5% |               Y |       2 |
| 201209 |    1 |    90 | -5.26% |               Y |       3 |
| 201210 |    1 |    85 | -5.56% |               Y |       4 |
| 201207 |    2 |    95 |     0% |               N |       0 |
| 201208 |    2 |   100 |  5.26% |               Y |       1 |
| 201209 |    2 |   103 |     3% |               N |       0 |
| 201210 |    2 |    99 | -3.88% |               Y |       1 |
+--------+------+-------+--------+-----------------+---------+

---

I hope someone can help me out!

Thanks in advance,

Paul

Answer 1

I solved it using just SQL. Here's how I did.

First of all, we need a query that, for each rows, shows the distance in rows from the last period:

Period  SecID  Price  Row
===============================
201206  1      105    4
201207  1      100    3
201208  1      95     2
201209  1      90     1
201210  1      85     0
201207  2      95     3
201208  2      100    2
201209  2      103    1
201210  2      99     0

we will call it PriceTable_Ordered:

SELECT
  PriceTable.Period,
  PriceTable.SecID,
  PriceTable.Price,
  (select count(*) from PriceTable PriceTable_1
   where PriceTable_1.SecID = PriceTable.SecID
   AND PriceTable_1.Period > PriceTable.Period) AS Row
FROM PriceTable;

Now to calculate the Delta, and showing if the Delta is more than the threesold, we can use this query that we will call PriceTable_Total1:

SELECT
  PriceTable_Ordered.*,
  PriceTable_Ordered_1.Price,
  (PriceTable_Ordered.Price-PriceTable_Ordered_1.Price)/(PriceTable_Ordered_1.Price) AS Delta,
  iif((ABS(Delta*100)>3),"Y","N") AS DeltaThreesold
FROM
  PriceTable_Ordered LEFT JOIN PriceTable_Ordered AS PriceTable_Ordered_1
  ON (PriceTable_Ordered.SecID = PriceTable_Ordered_1.SecID)
  AND (PriceTable_Ordered.[Row]=PriceTable_Ordered_1.[Row]-1);

And this returns:

Period  SecID  Price1  Row  Price2  Delta  DeltaThreesold
=========================================================
201206  1      105     4                   N
201207  1      100     3    105     -4,76  Y
201208  1      95      2    100     -0,05  Y
201209  1      90      1    95      -5,26  Y
201210  1      85      0    90      -5,55  Y
201207  2      95      3                   N
201208  2      100     2    95       5,26  Y
201209  2      103     1    100      0,03  N
201210  2      99      0    103     -3,88  Y

Now we can create PriceTable_Total2 based on PriceTable_Total1:

SELECT
  PriceTable_Total1.Period,
  PriceTable_Total1.SecID,
  PriceTable_Total1.PriceTable_Ordered.Price,
  PriceTable_Total1.Delta,
  PriceTable_Total1.DeltaThreesold,
  PriceTable_Total1.Row,
  (select min(row) from PriceTable_Total1 PriceTable_Total1_1
   where PriceTable_Total1.SecID = PriceTable_Total1_1.SecId
   and PriceTable_Total1.Row < PriceTable_Total1_1.Row
   and PriceTable_Total1_1.DeltaThreesold="N") AS MinN,
  IIf([DeltaThreesold]="Y",[MinN]-[row],0) AS CountRows
FROM PriceTable_Total1;

we select all the columns of PriceTable_Total1, then for each row we count the minimum row number > than current row where threesold is "N". If current row is over threesold, the count we need is just this difference, otherwise it's 0. Here's the result:

Period  SecID  Price  Delta  DelTh  Row  MinN  CountRows
========================================================
201206  1      105           N      4               0
201207  1      100    -4,76  Y      3    4          1
201208  1      95     -0,05  Y      2    4          2
201209  1      90     -5,26  Y      1    4          3
201210  1      85     -5,55  Y      0    4          4
201207  2      95            N      3               0
201208  2      100     5,26  Y      2    3          1
201209  2      103     0,03  N      1    3          0
201210  2      99     -3,88  Y      0    1          1

You can then hide the columns that you don't need. This query should work even if we cross the year and even if some periods are missing.

SELECT PriceTable_Total2.Period, PriceTable_Total2.SecID
FROM PriceTable_Total2
WHERE (PriceTable_Total2.Period=
        (select max(period)
         from PriceTable
         where PriceTable.SecID=PriceTable_Total2.SecID)
      AND (PriceTable_Total2.[CountRows])>=3);

this will return:

Period  SecID
201210  1

and that means that only SecID 1 is over threesold in the last period for more than 3 months.

I hope this answer is correct, it was nice to try to solve it!!

Answer 2

+1 for your intention of trying to get this in query itself without UDFs. Out of extreme interest I have put some effort to find a solution. I admit following code is not the most efficient code. (with all those IIFs, the performance is not that great)

Getting first 5 columns as per your above table are pretty straightforwad. I have saved that in qryDelta. I find the tricky part of the question is to have Counter in the same results table. Second query qryCounter will give you the final table as you expected.

qryDelta

   SELECT a.period, a.secid, a.price, 
   iif(isnull(ROUND((a.price-b.price)/b.price*100,2)),0, 
   ROUND((a.price-b.price)/b.price*100,2)) AS Delta, 
   iif(abs((a.price-b.price)/b.price)*100>3,"Y","N") AS Threshold, 
   SUM(iif(abs((a.price-b.price)/b.price)*100>3,1,0)) AS [Counter]
   FROM tbldelta AS a LEFT JOIN tbldelta AS b 
   ON (a.secid = b.secid) AND (a.period = b.period + 1)
   GROUP BY a.period, a.secid, a.price, 
   iif(isnull(ROUND((a.price-b.price)/b.price*100,2)),0,
   ROUND((a.price-b.price)/b.price*100,2)), 
   iif(abs((a.price-b.price)/b.price)*100>3,"Y","N")
   ORDER BY a.secid, a.period;

Results:

qryCounter

SELECT q.period, q.secid, q.price, q.delta, q.threshold, 
SUM(iif(q.counter=0,0,1)) AS Counter
FROM qryDelta q
LEFT JOIN tblDelta t
ON q.secid = t.secid
AND (t.period < q.period)
GROUP BY q.secid, q.period, q.price, q.delta, q.threshold

Results:

However I too faced the issue with SecId = 2, Period = 201208 with a total = 2. So I changed my query conditions. Now the results seem to show the cumulative periodic count properly except for SectID = 2, Period = 201210 total = 3. Perhpas you guys could throw some light to this. Out of most of the experiments done, it seems more or less a bug on JOIN and between dates that we are trying to put as coditions here.

PS: If you have decided to build user defined functions (UDF), then you may consider two things. Are you using Excel as front end or Access as front end. Then you have to provide necessary arrangements to call your Access UDF & query from Excel. If you are only using Access as both front and back end, then ofcourse using a UDF would be much easier to handle.

Answer 3

@Laurence: please find below the code

Public Function NextPer(Nperiod As Long) As Long
Dim Month As Long

If Not IsNull(Nperiod) Then
Month = 100 * ((Nperiod / 100) - Round(Nperiod / 100, 0))
If Month < 12 Then
NextPer = Nperiod + 1
Else
NextPer = Nperiod - Month + 101
End If
End If

End Function

Public Function PCount(SPeriod As Long, EPeriod As Long) As Long

Dim SMonth As Long
Dim EMonth As Long
Dim SYear As Long
Dim EYear As Long

If Not IsNull(SPeriod) And Not IsNull(EPeriod) Then
SMonth = 100 * ((SPeriod / 100) - Round(SPeriod / 100, 0))
SYear = (SPeriod - SMonth) / 100
EMonth = 100 * ((EPeriod / 100) - Round(EPeriod / 100, 0))
EYear = (EPeriod - EMonth) / 100
PCount = (12 * EYear + EMonth) - (12 * SYear + SMonth)
End If

End Function

And the QUERY (the parameters are for the moment hardcoded)

SELECT p0.SecurityID, p0.Period
FROM (PriceTable AS p0 
INNER JOIN PriceTable AS p1 ON (p0.SecurityID = p1.SecurityID)
AND (PCount(p0.Period,p1.Period)>=0) AND (PCount(p0.Period,p1.Period)<=2)) 
INNER JOIN PriceTable AS p2 ON (p1.SecurityID = p2.SecurityID) 
AND (p1.Period =   NextPer(p2.Period))
WHERE Abs(100*(p1.Price-p2.Price)/p2.Price)>0.03
GROUP BY p0.SecurityID, p0.Period
HAVING Count(*) = 3
ORDER BY p0.SecurityID asc , p0.Period asc;

Answer 4

I don't have Access to hand, but here's a query for SQL Server: The inner 'h' table is pretty much your helper table. the outer bit joins on 3 periods, and displays if the count with threshold 'Y' is 3 The way I did it you also need functions for working out the next period, and the number of periods between two end points. These should be fairly easy to write in VBA. You could also create a period table with a sequence number to work around this:

-- Function that works out the next period
-- i.e. if you supply 201112, it will return 201201
Create Function dbo.NextPeriod(@Period As Int) Returns Int As
Begin
  Declare
    @Month int,
    @Ret int = Null

  If @Period Is Not Null
  Begin
    Set @Month = @Period - 100 * (@Period / 100)
    If @Month < 12
      Set @Ret = @Period + 1
    Else
      Set @Ret = @Period - @Month + 101
  End
  Return @Ret
End;

-- Function that works out how many periods between the two endpoints
-- dbo.PeriodCount(201112, 201201) = 1
Create Function dbo.PeriodCount(@StartPeriod As Int, @EndPeriod As Int) Returns Int As
Begin
  Declare
    @StartMonth int,
    @EndMonth int,
    @StartYear int,
    @EndYear int,
    @Ret int = Null
  If @StartPeriod Is Not Null And @EndPeriod Is Not Null
  Begin
    Set @StartMonth = @StartPeriod - 100 * (@StartPeriod /100)
    Set @StartYear = (@StartPeriod - @StartMonth) / 100
    Set @EndMonth = @EndPeriod - 100 * (@EndPeriod / 100)
    Set @EndYear = (@EndPeriod - @EndMonth) / 100
    Set @Ret = (12 * @EndYear + @EndMonth) - (12 * @StartYear + @StartMonth)
  End
  Return @Ret
End;

-- Show periods that are the start of a run
-- of @Periods periods with threshold 
-- of at least @Threshold
Declare @Threshold Decimal(10, 2) = 3
Declare @Periods int = 3

Select
  p0.SecurityID,
  p0.Period
From
  PriceTable p0
    Inner Join (
      Select
        p1.*,
        100 * (p1.Price - p2.Price) / p2.Price As Delta,
        Case When Abs(100 * (p1.Price - p2.Price) / p2.Price) > @Threshold Then 'Y' Else 'N' End As OverThreshold
      From
        PriceTable p1
          Left Outer Join
        PriceTable p2
          On p1.SecurityID = p2.SecurityID And
             p1.Period = dbo.NextPeriod(p2.Period)
    ) h
    On p0.SecurityID = h.SecurityID And
       dbo.PeriodCount(p0.Period, h.Period) Between 0 And (@Periods - 1) And
       h.OverThreshold = 'Y'
Group By
  p0.SecurityID,
  p0.Period
Having
  Count(*) = @Periods 
Order By
  p0.SecurityID,
  p0.Period;

This shows you how the method works, you can simplify it like so:

Declare @Threshold Decimal(10, 2) = 3
Declare @Periods int = 3

Select
  p0.SecurityID,
  p0.Period
From
  PriceTable p0
    Inner Join
  PriceTable p1
    On p0.SecurityID = p1.SecurityID And
       dbo.PeriodCount(p0.Period, p1.Period) Between 0 And (@Periods - 1)
    Inner Join
  PriceTable p2
    On p1.SecurityID = p2.SecurityID And
       p1.Period = dbo.NextPeriod(p2.Period)
Where
  Abs(100 * (p1.Price - p2.Price) / p2.Price) > @Threshold
Group By
  p0.SecurityID,
  p0.Period
Having
  Count(*) = @Periods 
Order By
  p0.SecurityID,
  p0.Period;

http://sqlfiddle.com/#!3/8eff9/2