-1

I would like to have the SQL code that will allow me to form quintile portfolios for monthly stock data. The formation of the quintile portfolios depend on a ratio (called B/M in my spreadsheet). I would like that the code automatically generates for each month different quintile portfolios, as stocks/companies are added or withdrawn for a certain month compared the previous month. Ratios can also change so that for the following month a certain stock can be ranked in another quintile.

I added a printscreen to briefly show how I organized my excel sheet. Basically, it is sorted per month.enter image description here

Brieuc Feneuil
  • 9
  • 2
  • I can't see your image. What formula do you currently use to calculate the ratio? You may be able to use window functions (https://learn.microsoft.com/en-us/sql/t-sql/functions/ranking-functions-transact-sql) to calculate quintiles. What flavor and version of SQL are you using? – Shawn Jul 28 '17 at 19:06
  • Also, https://stackoverflow.com/help/mcve <<< Very helpful. – Shawn Jul 28 '17 at 19:09

2 Answers2

0

NOTE: This applies to MS SQL 2008+.

I don't know what your data structure looks like, but maybe something along the lines of 

/* Test Data */
WITH stocks AS (
    /* Jan = 10 = 2 per quint */
    SELECT 'abc' AS StockName, 100.00 AS StockPrice, '20170101' AS PriceDate UNION ALL
    SELECT 'def' AS StockName, 99.00 AS StockPrice, '20170101' AS PriceDate UNION ALL
    SELECT 'ghi' AS StockName, 50.00 AS StockPrice, '20170101' AS PriceDate UNION ALL
    SELECT 'jkl' AS StockName, 50.00 AS StockPrice, '20170101' AS PriceDate UNION ALL
    SELECT 'mno' AS StockName, 75.00 AS StockPrice, '20170101' AS PriceDate UNION ALL
    SELECT 'pqr' AS StockName, 77.00 AS StockPrice, '20170101' AS PriceDate UNION ALL
    SELECT 'stu' AS StockName, 20.00 AS StockPrice, '20170101' AS PriceDate UNION ALL
    SELECT 'vwx' AS StockName, 10.00 AS StockPrice, '20170101' AS PriceDate UNION ALL
    SELECT 'yz1' AS StockName,  2.00 AS StockPrice, '20170101' AS PriceDate UNION ALL
    SELECT '234' AS StockName,  1.00 AS StockPrice, '20170101' AS PriceDate UNION ALL

    /* Feb = 7 = uneven quints */
    SELECT 'abc' AS StockName, 1.00 AS StockPrice, '20170201' AS PriceDate UNION ALL
    SELECT 'def' AS StockName, 2.00 AS StockPrice, '20170201' AS PriceDate UNION ALL
    SELECT 'ghi' AS StockName, 20.00 AS StockPrice, '20170201' AS PriceDate UNION ALL
    SELECT 'jkl' AS StockName, 55.00 AS StockPrice, '20170201' AS PriceDate UNION ALL
    SELECT 'mno' AS StockName, 50.00 AS StockPrice, '20170201' AS PriceDate UNION ALL
    SELECT 'pqr' AS StockName, 100.00 AS StockPrice, '20170201' AS PriceDate UNION ALL
    SELECT 'stu' AS StockName, 90.00 AS StockPrice, '20170201' AS PriceDate UNION ALL

    /* Mar = 3 = not enough for 5 quints. */
    SELECT 'abc' AS StockName, 42.00 AS StockPrice, '20170301' AS PriceDate UNION ALL
    SELECT 'jkl' AS StockName, 42.00 AS StockPrice, '20170301' AS PriceDate UNION ALL
    SELECT 'vwx' AS StockName, 42.00 AS StockPrice, '20170301' AS PriceDate
)

/* Query */    
SELECT y.StockName, y.StockPrice, y.PriceMonth, y.quintile
FROM (
    SELECT x.StockName, x.StockPrice, month(x.PriceDate) AS PriceMonth 
        , NTILE(5) OVER (PARTITION BY month(x.PriceDate) ORDER BY x.StockPrice DESC) AS quintile
    FROM stocks x
    GROUP BY x.StockName, x.StockPrice, month(x.PriceDate)
) y
ORDER BY y.PriceMonth, y.quintile ASC

Gives you

StockName  StockPrice  PriceMonth  quintile
abc        100.00      1           1
def         99.00      1           1
pqr         77.00      1           2
mno         75.00      1           2
ghi         50.00      1           3
jkl         50.00      1           3
stu         20.00      1           4
vwx         10.00      1           4
yz1          2.00      1           5
234          1.00      1           5
pqr        100.00      2           1
stu         90.00      2           1
jkl         55.00      2           2
mno         50.00      2           2
ghi         20.00      2           3
def          2.00      2           4
abc          1.00      2           5
abc         42.00      3           1
jkl         42.00      3           2
vwx         42.00      3           3

Then when you display it, you can just sort/group it by the quintile.

Also, my example above illustrates how NTILE() may not necessarily give you what you're looking for. You may have to calculate then create quintiles yourself. See March group >> all are $42, yet they got put into 3 different quintiles. It's also below the other Quintile 3 Prices. So check it's what you want.

Lastly, it would be better to add a Date Dimension Table that pre-calculates the date parts for you, and then JOIN that to your main sub-query, but that is a whole different discussion.

Shawn
  • 4,758
  • 1
  • 20
  • 29
0

If you calculate ratios and you can use those to determine which quintile something is supposed to be in, it makes it much easier. You don't have to use NTILE(), you can just set up a CTE that specifies the min and max ratios that will fall into each quintile.

/* Test Data */
IF OBJECT_ID(N'tempdb..#stocks') IS NOT NULL
     DROP TABLE #stocks
;

CREATE TABLE #stocks  (StockName varchar(10), BMratio int, StockMonth datetime) ;
INSERT INTO #stocks (StockName, BMratio, StockMonth)
    /* Jan = 10 = 2 per quint */
    SELECT 'abc' AS StockName, 10 AS BMratio, '20170101' AS StockMonth UNION ALL
    SELECT 'def' AS StockName,  9 AS BMratio, '20170101' AS StockMonth UNION ALL
    SELECT 'ghi' AS StockName,  8 AS BMratio, '20170101' AS StockMonth UNION ALL
    SELECT 'jkl' AS StockName,  7 AS BMratio, '20170101' AS StockMonth UNION ALL
    SELECT 'mno' AS StockName,  6 AS BMratio, '20170101' AS StockMonth UNION ALL
    SELECT 'pqr' AS StockName,  5 AS BMratio, '20170101' AS StockMonth UNION ALL
    SELECT 'stu' AS StockName,  4 AS BMratio, '20170101' AS StockMonth UNION ALL
    SELECT 'vwx' AS StockName,  3 AS BMratio, '20170101' AS StockMonth UNION ALL
    SELECT 'yz1' AS StockName,  2 AS BMratio, '20170101' AS StockMonth UNION ALL
    SELECT '234' AS StockName,  1 AS BMratio, '20170101' AS StockMonth UNION ALL

    /* Feb = 7 = uneven quints */
    SELECT 'abc' AS StockName,  1 AS BMratio, '20170201' AS StockMonth UNION ALL
    SELECT 'def' AS StockName,  2 AS BMratio, '20170201' AS StockMonth UNION ALL
    SELECT 'ghi' AS StockName,  4 AS BMratio, '20170201' AS StockMonth UNION ALL
    SELECT 'jkl' AS StockName,  5 AS BMratio, '20170201' AS StockMonth UNION ALL
    SELECT 'mno' AS StockName,  7 AS BMratio, '20170201' AS StockMonth UNION ALL
    SELECT 'pqr' AS StockName, 10 AS BMratio, '20170201' AS StockMonth UNION ALL
    SELECT 'stu' AS StockName,  9 AS BMratio, '20170201' AS StockMonth UNION ALL

    /* Mar = 3 = not enough for 5 quints. */
    SELECT 'abc' AS StockName, 5 AS BMratio, '20170301' AS StockMonth UNION ALL
    SELECT 'jkl' AS StockName, 5 AS BMratio, '20170301' AS StockMonth UNION ALL
    SELECT 'vwx' AS StockName, 5 AS BMratio, '20170301' AS StockMonth
; 

/* Create CTE query */
; WITH Quint_CTE AS (
    SELECT 1 AS quintNum, 9 AS quintMin, 10 AS quintMax UNION ALL
    SELECT 2 AS quintNum, 7 AS quintMin,  8 AS quintMax UNION ALL
    SELECT 3 AS quintNum, 5 AS quintMin,  6 AS quintMax UNION ALL
    SELECT 4 AS quintNum, 3 AS quintMin,  4 AS quintMax UNION ALL
    SELECT 5 AS quintNum, 0 AS quintMin,  2 AS quintMax 
)
SELECT x.StockName, month(x.StockMonth) AS StockMonth, q.quintNum AS quintile
FROM #stocks x
INNER JOIN Quint_CTE q ON x.BMratio BETWEEN q.quintMin AND q.quintMax
ORDER BY StockMonth, q.quintNum ASC
Shawn
  • 4,758
  • 1
  • 20
  • 29