MovingVOLATILITY
Updated 2023-11-13 21:58:14.430000
Syntax
SELECT [westclintech].[wct].[MovingVOLATILITY](
<@Price, float,>
,<@Scale, float,>
,<@Offset, int,>
,<@RowNum, int,>
,<@Id, tinyint,>
,<@Exact, bit,>)
Description
Use the scalar function MovingVOLATILITY to calculate the historical volatility based upon price or valuation data from column values in an ordered resultant table, without the need for a self-join. The volatility is calculated for each value from the first value in the window to the last value in the window. If the column values are presented to the functions out of order, an error message will be generated.
The historic volatility is calculated as the sample standard deviation of the natural logarithm of the returns multiplied by the square of the scaling factor supplied to the function.
Arguments
@Id
a unique identifier for the MovingVOLATILITY calculation. @Id allows you to specify multiple moving sums within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.
@Offset
specifies the window size. The window size (or the number of rows included in the result) is the current row plus the @Offset. Since the volatility calculation is based in returns, the window-size needs to account for the initial price; in other words, 30 days of returns requires 31 days of prices. @Offset is an expression of type int or of a type that can be implicitly converted to int.
@Exact
a bit value which tells the function whether or not to return a NULL value if the number of rows in the window is smaller the @Offset value. If @Exact is 'True' and the number of rows in the window is less the @Offset then a NULL is returned. @Exact is an expression of type bit or of a type that can be implicitly converted to bit.
@Scale
the scaling factor used in the calculation. @Scale is an expression of type float or of a type that can be implicitly converted to float.
@RowNum
the number of the row within the group for which the sum is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.
@Price
the price passed into the function. Generally, price is the end-of-day price for the security, commodity, currency, or index for which the volatility is being calculated. @Price is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
If @Id is NULL then @Id = 0.
To calculate the running volatility from the beginning of a dataset or partition, use the RunningVOLATILITY function.
If @RowNum is equal to 1, MovingVOLATILITY is equal to NULL.
@RowNum must be in ascending order.
There may be cases where the order in which the data are returned to the function and the order in which the results are returned are different, generally due to parallelism. You can use OPTION(MAXDOP 1) or OPTION(MAXDOP 1,FORCE ORDER) to help eliminate this problem.
Examples
In this example we calculate the 30-day moving volatility.
SELECT ticker,
tdate,
CAST(price as money) as price,
wct.MovingVOLATILITY( price, --@Price
252, --@Scale
30, --@Offset
ROW_NUMBER() OVER (ORDER BY tdate), --@RowNum
NULL, --@Id
'True' --@Exact
) as VOL
FROM
(
VALUES
('IBM', '2013-12-27', 185.08),
('IBM', '2013-12-26', 185.35),
('IBM', '2013-12-24', 183.22),
('IBM', '2013-12-23', 182.23),
('IBM', '2013-12-20', 180.02),
('IBM', '2013-12-19', 180.22),
('IBM', '2013-12-18', 178.7),
('IBM', '2013-12-17', 175.76),
('IBM', '2013-12-16', 177.85),
('IBM', '2013-12-13', 172.8),
('IBM', '2013-12-12', 173.37),
('IBM', '2013-12-11', 175.2),
('IBM', '2013-12-10', 177.12),
('IBM', '2013-12-09', 177.46),
('IBM', '2013-12-06', 177.67),
('IBM', '2013-12-05', 176.08),
('IBM', '2013-12-04', 175.74),
('IBM', '2013-12-03', 176.08),
('IBM', '2013-12-02', 177.48),
('IBM', '2013-11-29', 179.68),
('IBM', '2013-11-27', 178.97),
('IBM', '2013-11-26', 177.31),
('IBM', '2013-11-25', 178.94),
('IBM', '2013-11-22', 181.3),
('IBM', '2013-11-21', 184.13),
('IBM', '2013-11-20', 185.19),
('IBM', '2013-11-19', 185.25),
('IBM', '2013-11-18', 184.47),
('IBM', '2013-11-15', 183.19),
('IBM', '2013-11-14', 182.21),
('IBM', '2013-11-13', 183.55),
('IBM', '2013-11-12', 183.07),
('IBM', '2013-11-11', 182.88),
('IBM', '2013-11-08', 179.99),
('IBM', '2013-11-07', 180),
('IBM', '2013-11-06', 179.19),
('IBM', '2013-11-05', 176.9),
('IBM', '2013-11-04', 179.31),
('IBM', '2013-11-01', 178.27),
('IBM', '2013-10-31', 178.25),
('IBM', '2013-10-30', 179.19),
('IBM', '2013-10-29', 181.15),
('IBM', '2013-10-28', 176.4),
('IBM', '2013-10-25', 175.91),
('IBM', '2013-10-24', 176.85),
('IBM', '2013-10-23', 174.83),
('IBM', '2013-10-22', 174.04),
('IBM', '2013-10-21', 171.94),
('IBM', '2013-10-18', 172.85),
('IBM', '2013-10-17', 173.9),
('IBM', '2013-10-16', 185.73),
('IBM', '2013-10-15', 183.67),
('IBM', '2013-10-14', 185.97),
('IBM', '2013-10-11', 185.17),
('IBM', '2013-10-10', 183.78),
('IBM', '2013-10-09', 180.35),
('IBM', '2013-10-08', 177.77),
('IBM', '2013-10-07', 181.04),
('IBM', '2013-10-04', 183.12),
('IBM', '2013-10-03', 182.88),
('IBM', '2013-10-02', 183.97),
('IBM', '2013-10-01', 185.38),
('IBM', '2013-09-30', 184.19)
) n (ticker, tdate, price);
This produces the following result.
{"columns":[{"field":"ticker"},{"field":"tdate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"VOL"}],"rows":[{"ticker":"IBM","tdate":"2013-09-30","price":"184.19","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-01","price":"185.38","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-02","price":"183.97","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-03","price":"182.88","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-04","price":"183.12","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-07","price":"181.04","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-08","price":"177.77","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-09","price":"180.35","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-10","price":"183.78","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-11","price":"185.17","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-14","price":"185.97","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-15","price":"183.67","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-16","price":"185.73","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-17","price":"173.90","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-18","price":"172.85","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-21","price":"171.94","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-22","price":"174.04","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-23","price":"174.83","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-24","price":"176.85","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-25","price":"175.91","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-28","price":"176.40","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-29","price":"181.15","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-30","price":"179.19","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-31","price":"178.25","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-11-01","price":"178.27","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-11-04","price":"179.31","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-11-05","price":"176.90","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-11-06","price":"179.19","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-11-07","price":"180.00","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-11-08","price":"179.99","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-11-11","price":"182.88","VOL":"0.259600756641362"},{"ticker":"IBM","tdate":"2013-11-12","price":"183.07","VOL":"0.25886435068979"},{"ticker":"IBM","tdate":"2013-11-13","price":"183.55","VOL":"0.258084881217436"},{"ticker":"IBM","tdate":"2013-11-14","price":"182.21","VOL":"0.25838941695409"},{"ticker":"IBM","tdate":"2013-11-15","price":"183.19","VOL":"0.258851361662631"},{"ticker":"IBM","tdate":"2013-11-18","price":"184.47","VOL":"0.257272874238383"},{"ticker":"IBM","tdate":"2013-11-19","price":"185.25","VOL":"0.25113141270427"},{"ticker":"IBM","tdate":"2013-11-20","price":"185.19","VOL":"0.248098166203494"},{"ticker":"IBM","tdate":"2013-11-21","price":"184.13","VOL":"0.242810179393185"},{"ticker":"IBM","tdate":"2013-11-22","price":"181.30","VOL":"0.245804723406176"},{"ticker":"IBM","tdate":"2013-11-25","price":"178.94","VOL":"0.247889849528801"},{"ticker":"IBM","tdate":"2013-11-26","price":"177.31","VOL":"0.246782618938391"},{"ticker":"IBM","tdate":"2013-11-27","price":"178.97","VOL":"0.24604234701296"},{"ticker":"IBM","tdate":"2013-11-29","price":"179.68","VOL":"0.152062274334431"},{"ticker":"IBM","tdate":"2013-12-02","price":"177.48","VOL":"0.155660954549234"},{"ticker":"IBM","tdate":"2013-12-03","price":"176.08","VOL":"0.156753405261162"},{"ticker":"IBM","tdate":"2013-12-04","price":"175.74","VOL":"0.153166929494441"},{"ticker":"IBM","tdate":"2013-12-05","price":"176.08","VOL":"0.152731802077581"},{"ticker":"IBM","tdate":"2013-12-06","price":"177.67","VOL":"0.151297530686739"},{"ticker":"IBM","tdate":"2013-12-09","price":"177.46","VOL":"0.15046658789529"},{"ticker":"IBM","tdate":"2013-12-10","price":"177.12","VOL":"0.15040745529138"},{"ticker":"IBM","tdate":"2013-12-11","price":"175.20","VOL":"0.131153490377389"},{"ticker":"IBM","tdate":"2013-12-12","price":"173.37","VOL":"0.13091290987267"},{"ticker":"IBM","tdate":"2013-12-13","price":"172.80","VOL":"0.130493430608268"},{"ticker":"IBM","tdate":"2013-12-16","price":"177.85","VOL":"0.15657740328222"},{"ticker":"IBM","tdate":"2013-12-17","price":"175.76","VOL":"0.159130504552285"},{"ticker":"IBM","tdate":"2013-12-18","price":"178.70","VOL":"0.161891633259364"},{"ticker":"IBM","tdate":"2013-12-19","price":"180.22","VOL":"0.159420520640619"},{"ticker":"IBM","tdate":"2013-12-20","price":"180.02","VOL":"0.158928812684109"},{"ticker":"IBM","tdate":"2013-12-23","price":"182.23","VOL":"0.162812027777703"},{"ticker":"IBM","tdate":"2013-12-24","price":"183.22","VOL":"0.156848086287117"},{"ticker":"IBM","tdate":"2013-12-26","price":"185.35","VOL":"0.160341722727633"},{"ticker":"IBM","tdate":"2013-12-27","price":"185.08","VOL":"0.160289637318093"}]}
In this example, we calculate the 30-day moving volatility for multiple securities.
SELECT ticker,
tdate,
CAST(price as money) as price,
wct.MovingVOLATILITY( price,
--@Price
252, --@Scale
30, --@Offset
ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY
ticker, tdate), --@RowNum
NULL, --@Id
'True' --@Exact
) as VOL
FROM
(
VALUES
('IBM', '2013-12-27', 185.08),
('IBM', '2013-12-26', 185.35),
('IBM', '2013-12-24', 183.22),
('IBM', '2013-12-23', 182.23),
('IBM', '2013-12-20', 180.02),
('IBM', '2013-12-19', 180.22),
('IBM', '2013-12-18', 178.7),
('IBM', '2013-12-17', 175.76),
('IBM', '2013-12-16', 177.85),
('IBM', '2013-12-13', 172.8),
('IBM', '2013-12-12', 173.37),
('IBM', '2013-12-11', 175.2),
('IBM', '2013-12-10', 177.12),
('IBM', '2013-12-09', 177.46),
('IBM', '2013-12-06', 177.67),
('IBM', '2013-12-05', 176.08),
('IBM', '2013-12-04', 175.74),
('IBM', '2013-12-03', 176.08),
('IBM', '2013-12-02', 177.48),
('IBM', '2013-11-29', 179.68),
('IBM', '2013-11-27', 178.97),
('IBM', '2013-11-26', 177.31),
('IBM', '2013-11-25', 178.94),
('IBM', '2013-11-22', 181.3),
('IBM', '2013-11-21', 184.13),
('IBM', '2013-11-20', 185.19),
('IBM', '2013-11-19', 185.25),
('IBM', '2013-11-18', 184.47),
('IBM', '2013-11-15', 183.19),
('IBM', '2013-11-14', 182.21),
('IBM', '2013-11-13', 183.55),
('IBM', '2013-11-12', 183.07),
('IBM', '2013-11-11', 182.88),
('IBM', '2013-11-08', 179.99),
('IBM', '2013-11-07', 180),
('IBM', '2013-11-06', 179.19),
('IBM', '2013-11-05', 176.9),
('IBM', '2013-11-04', 179.31),
('IBM', '2013-11-01', 178.27),
('IBM', '2013-10-31', 178.25),
('IBM', '2013-10-30', 179.19),
('IBM', '2013-10-29', 181.15),
('IBM', '2013-10-28', 176.4),
('IBM', '2013-10-25', 175.91),
('IBM', '2013-10-24', 176.85),
('IBM', '2013-10-23', 174.83),
('IBM', '2013-10-22', 174.04),
('IBM', '2013-10-21', 171.94),
('IBM', '2013-10-18', 172.85),
('IBM', '2013-10-17', 173.9),
('IBM', '2013-10-16', 185.73),
('IBM', '2013-10-15', 183.67),
('IBM', '2013-10-14', 185.97),
('IBM', '2013-10-11', 185.17),
('IBM', '2013-10-10', 183.78),
('IBM', '2013-10-09', 180.35),
('IBM', '2013-10-08', 177.77),
('IBM', '2013-10-07', 181.04),
('IBM', '2013-10-04', 183.12),
('IBM', '2013-10-03', 182.88),
('IBM', '2013-10-02', 183.97),
('IBM', '2013-10-01', 185.38),
('IBM', '2013-09-30', 184.19),
('FB', '2013-12-27', 55.44),
('FB', '2013-12-26', 57.73),
('FB', '2013-12-24', 57.96),
('FB', '2013-12-23', 57.77),
('FB', '2013-12-20', 55.12),
('FB', '2013-12-19', 55.05),
('FB', '2013-12-18', 55.57),
('FB', '2013-12-17', 54.86),
('FB', '2013-12-16', 53.81),
('FB', '2013-12-13', 53.32),
('FB', '2013-12-12', 51.83),
('FB', '2013-12-11', 49.38),
('FB', '2013-12-10', 50.25),
('FB', '2013-12-09', 48.84),
('FB', '2013-12-06', 47.94),
('FB', '2013-12-05', 48.34),
('FB', '2013-12-04', 48.62),
('FB', '2013-12-03', 46.73),
('FB', '2013-12-02', 47.06),
('FB', '2013-11-29', 47.01),
('FB', '2013-11-27', 46.49),
('FB', '2013-11-26', 45.89),
('FB', '2013-11-25', 44.82),
('FB', '2013-11-22', 46.23),
('FB', '2013-11-21', 46.7),
('FB', '2013-11-20', 46.43),
('FB', '2013-11-19', 46.36),
('FB', '2013-11-18', 45.83),
('FB', '2013-11-15', 49.01),
('FB', '2013-11-14', 48.99),
('FB', '2013-11-13', 48.71),
('FB', '2013-11-12', 46.61),
('FB', '2013-11-11', 46.2),
('FB', '2013-11-08', 47.53),
('FB', '2013-11-07', 47.56),
('FB', '2013-11-06', 49.12),
('FB', '2013-11-05', 50.11),
('FB', '2013-11-04', 48.22),
('FB', '2013-11-01', 49.75),
('FB', '2013-10-31', 50.21),
('FB', '2013-10-30', 49.01),
('FB', '2013-10-29', 49.4),
('FB', '2013-10-28', 50.23),
('FB', '2013-10-25', 51.95),
('FB', '2013-10-24', 52.45),
('FB', '2013-10-23', 51.9),
('FB', '2013-10-22', 52.68),
('FB', '2013-10-21', 53.85),
('FB', '2013-10-18', 54.22),
('FB', '2013-10-17', 52.21),
('FB', '2013-10-16', 51.14),
('FB', '2013-10-15', 49.5),
('FB', '2013-10-14', 49.51),
('FB', '2013-10-11', 49.11),
('FB', '2013-10-10', 49.05),
('FB', '2013-10-09', 46.77),
('FB', '2013-10-08', 47.14),
('FB', '2013-10-07', 50.52),
('FB', '2013-10-04', 51.04),
('FB', '2013-10-03', 49.18),
('FB', '2013-10-02', 50.28),
('FB', '2013-10-01', 50.42),
('FB', '2013-09-30', 50.23),
('ORCL', '2013-12-27', 37.98),
('ORCL', '2013-12-26', 37.69),
('ORCL', '2013-12-24', 37.32),
('ORCL', '2013-12-23', 36.93),
('ORCL', '2013-12-20', 36.37),
('ORCL', '2013-12-19', 36.6),
('ORCL', '2013-12-18', 34.6),
('ORCL', '2013-12-17', 33.63),
('ORCL', '2013-12-16', 33.54),
('ORCL', '2013-12-13', 33.23),
('ORCL', '2013-12-12', 33.6),
('ORCL', '2013-12-11', 34.56),
('ORCL', '2013-12-10', 34.8),
('ORCL', '2013-12-09', 35.6),
('ORCL', '2013-12-06', 35.48),
('ORCL', '2013-12-05', 34.85),
('ORCL', '2013-12-04', 35.07),
('ORCL', '2013-12-03', 35.07),
('ORCL', '2013-12-02', 35.08),
('ORCL', '2013-11-29', 35.29),
('ORCL', '2013-11-27', 35.29),
('ORCL', '2013-11-26', 34.93),
('ORCL', '2013-11-25', 34.78),
('ORCL', '2013-11-22', 34.83),
('ORCL', '2013-11-21', 34.94),
('ORCL', '2013-11-20', 34.75),
('ORCL', '2013-11-19', 34.76),
('ORCL', '2013-11-18', 34.93),
('ORCL', '2013-11-15', 34.92),
('ORCL', '2013-11-14', 34.38),
('ORCL', '2013-11-13', 35),
('ORCL', '2013-11-12', 34.7),
('ORCL', '2013-11-11', 34.37),
('ORCL', '2013-11-08', 34.35),
('ORCL', '2013-11-07', 34),
('ORCL', '2013-11-06', 34.07),
('ORCL', '2013-11-05', 33.5),
('ORCL', '2013-11-04', 33.71),
('ORCL', '2013-11-01', 33.53),
('ORCL', '2013-10-31', 33.5),
('ORCL', '2013-10-30', 33.53),
('ORCL', '2013-10-29', 33.71),
('ORCL', '2013-10-28', 33.14),
('ORCL', '2013-10-25', 33.15),
('ORCL', '2013-10-24', 33.07),
('ORCL', '2013-10-23', 32.7),
('ORCL', '2013-10-22', 32.9),
('ORCL', '2013-10-21', 32.95),
('ORCL', '2013-10-18', 32.9),
('ORCL', '2013-10-17', 32.87),
('ORCL', '2013-10-16', 33.02),
('ORCL', '2013-10-15', 32.75),
('ORCL', '2013-10-14', 33.28),
('ORCL', '2013-10-11', 33.26),
('ORCL', '2013-10-10', 32.99),
('ORCL', '2013-10-09', 32.19),
('ORCL', '2013-10-08', 32.37),
('ORCL', '2013-10-07', 32.84),
('ORCL', '2013-10-04', 33.21),
('ORCL', '2013-10-03', 33.12),
('ORCL', '2013-10-02', 33.56),
('ORCL', '2013-10-01', 33.38),
('ORCL', '2013-09-30', 33.05),
('MSFT', '2013-12-27', 37.29),
('MSFT', '2013-12-26', 37.44),
('MSFT', '2013-12-24', 37.08),
('MSFT', '2013-12-23', 36.62),
('MSFT', '2013-12-20', 36.8),
('MSFT', '2013-12-19', 36.25),
('MSFT', '2013-12-18', 36.58),
('MSFT', '2013-12-17', 36.52),
('MSFT', '2013-12-16', 36.89),
('MSFT', '2013-12-13', 36.69),
('MSFT', '2013-12-12', 37.22),
('MSFT', '2013-12-11', 37.61),
('MSFT', '2013-12-10', 38.11),
('MSFT', '2013-12-09', 38.71),
('MSFT', '2013-12-06', 38.36),
('MSFT', '2013-12-05', 38),
('MSFT', '2013-12-04', 38.94),
('MSFT', '2013-12-03', 38.31),
('MSFT', '2013-12-02', 38.45),
('MSFT', '2013-11-29', 38.13),
('MSFT', '2013-11-27', 37.6),
('MSFT', '2013-11-26', 37.35),
('MSFT', '2013-11-25', 37.64),
('MSFT', '2013-11-22', 37.57),
('MSFT', '2013-11-21', 37.4),
('MSFT', '2013-11-20', 37.08),
('MSFT', '2013-11-19', 36.74),
('MSFT', '2013-11-18', 36.92),
('MSFT', '2013-11-15', 37.56),
('MSFT', '2013-11-14', 37.73),
('MSFT', '2013-11-13', 37.87),
('MSFT', '2013-11-12', 37.08),
('MSFT', '2013-11-11', 37.31),
('MSFT', '2013-11-08', 37.5),
('MSFT', '2013-11-07', 37.22),
('MSFT', '2013-11-06', 37.89),
('MSFT', '2013-11-05', 36.36),
('MSFT', '2013-11-04', 35.67),
('MSFT', '2013-11-01', 35.26),
('MSFT', '2013-10-31', 35.14),
('MSFT', '2013-10-30', 35.27),
('MSFT', '2013-10-29', 35.25),
('MSFT', '2013-10-28', 35.3),
('MSFT', '2013-10-25', 35.46),
('MSFT', '2013-10-24', 33.47),
('MSFT', '2013-10-23', 33.51),
('MSFT', '2013-10-22', 34.32),
('MSFT', '2013-10-21', 34.73),
('MSFT', '2013-10-18', 34.7),
('MSFT', '2013-10-17', 34.66),
('MSFT', '2013-10-16', 34.38),
('MSFT', '2013-10-15', 34.23),
('MSFT', '2013-10-14', 34.19),
('MSFT', '2013-10-11', 33.87),
('MSFT', '2013-10-10', 33.51),
('MSFT', '2013-10-09', 32.82),
('MSFT', '2013-10-08', 32.76),
('MSFT', '2013-10-07', 33.05),
('MSFT', '2013-10-04', 33.62),
('MSFT', '2013-10-03', 33.61),
('MSFT', '2013-10-02', 33.66),
('MSFT', '2013-10-01', 33.33),
('MSFT', '2013-09-30', 33.03),
('AAPL', '2013-12-27', 560.09),
('AAPL', '2013-12-26', 563.9),
('AAPL', '2013-12-24', 567.67),
('AAPL', '2013-12-23', 570.09),
('AAPL', '2013-12-20', 549.02),
('AAPL', '2013-12-19', 544.46),
('AAPL', '2013-12-18', 550.77),
('AAPL', '2013-12-17', 554.99),
('AAPL', '2013-12-16', 557.5),
('AAPL', '2013-12-13', 554.43),
('AAPL', '2013-12-12', 560.54),
('AAPL', '2013-12-11', 561.36),
('AAPL', '2013-12-10', 565.55),
('AAPL', '2013-12-09', 566.43),
('AAPL', '2013-12-06', 560.02),
('AAPL', '2013-12-05', 567.9),
('AAPL', '2013-12-04', 565),
('AAPL', '2013-12-03', 566.32),
('AAPL', '2013-12-02', 551.23),
('AAPL', '2013-11-29', 556.07),
('AAPL', '2013-11-27', 545.96),
('AAPL', '2013-11-26', 533.4),
('AAPL', '2013-11-25', 523.74),
('AAPL', '2013-11-22', 519.8),
('AAPL', '2013-11-21', 521.14),
('AAPL', '2013-11-20', 515),
('AAPL', '2013-11-19', 519.55),
('AAPL', '2013-11-18', 518.63),
('AAPL', '2013-11-15', 524.99),
('AAPL', '2013-11-14', 528.16),
('AAPL', '2013-11-13', 520.63),
('AAPL', '2013-11-12', 520.01),
('AAPL', '2013-11-11', 519.05),
('AAPL', '2013-11-08', 520.56),
('AAPL', '2013-11-07', 512.49),
('AAPL', '2013-11-06', 520.92),
('AAPL', '2013-11-05', 522.4),
('AAPL', '2013-11-04', 523.69),
('AAPL', '2013-11-01', 517.01),
('AAPL', '2013-10-31', 519.67),
('AAPL', '2013-10-30', 521.85),
('AAPL', '2013-10-29', 513.68),
('AAPL', '2013-10-28', 526.8),
('AAPL', '2013-10-25', 522.91),
('AAPL', '2013-10-24', 528.82),
('AAPL', '2013-10-23', 521.91),
('AAPL', '2013-10-22', 516.85),
('AAPL', '2013-10-21', 518.33),
('AAPL', '2013-10-18', 505.94),
('AAPL', '2013-10-17', 501.57),
('AAPL', '2013-10-16', 498.2),
('AAPL', '2013-10-15', 495.79),
('AAPL', '2013-10-14', 493.16),
('AAPL', '2013-10-11', 489.95),
('AAPL', '2013-10-10', 486.8),
('AAPL', '2013-10-09', 483.77),
('AAPL', '2013-10-08', 478.15),
('AAPL', '2013-10-07', 484.92),
('AAPL', '2013-10-04', 480.23),
('AAPL', '2013-10-03', 480.6),
('AAPL', '2013-10-02', 486.72),
('AAPL', '2013-10-01', 485.13),
('AAPL', '2013-09-30', 473.98)
) n (ticker, tdate, price);
This produces the following result.
{"columns":[{"field":"ticker"},{"field":"tdate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"VOL"}],"rows":[{"ticker":"AAPL","tdate":"2013-09-30","price":"473.98","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-01","price":"485.13","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-02","price":"486.72","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-03","price":"480.60","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-04","price":"480.23","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-07","price":"484.92","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-08","price":"478.15","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-09","price":"483.77","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-10","price":"486.80","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-11","price":"489.95","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-14","price":"493.16","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-15","price":"495.79","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-16","price":"498.20","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-17","price":"501.57","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-18","price":"505.94","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-21","price":"518.33","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-22","price":"516.85","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-23","price":"521.91","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-24","price":"528.82","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-25","price":"522.91","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-28","price":"526.80","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-29","price":"513.68","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-30","price":"521.85","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-10-31","price":"519.67","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-11-01","price":"517.01","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-11-04","price":"523.69","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-11-05","price":"522.40","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-11-06","price":"520.92","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-11-07","price":"512.49","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-11-08","price":"520.56","VOL":"NULL"},{"ticker":"AAPL","tdate":"2013-11-11","price":"519.05","VOL":"0.182448898566812"},{"ticker":"AAPL","tdate":"2013-11-12","price":"520.01","VOL":"0.172083895254781"},{"ticker":"AAPL","tdate":"2013-11-13","price":"520.63","VOL":"0.172088922541504"},{"ticker":"AAPL","tdate":"2013-11-14","price":"528.16","VOL":"0.169557381867762"},{"ticker":"AAPL","tdate":"2013-11-15","price":"524.99","VOL":"0.171284793834041"},{"ticker":"AAPL","tdate":"2013-11-18","price":"518.63","VOL":"0.175500927555936"},{"ticker":"AAPL","tdate":"2013-11-19","price":"519.55","VOL":"0.168585936287561"},{"ticker":"AAPL","tdate":"2013-11-20","price":"515.00","VOL":"0.169619590467246"},{"ticker":"AAPL","tdate":"2013-11-21","price":"521.14","VOL":"0.17158174055262"},{"ticker":"AAPL","tdate":"2013-11-22","price":"519.80","VOL":"0.171665850721973"},{"ticker":"AAPL","tdate":"2013-11-25","price":"523.74","VOL":"0.171926757898195"},{"ticker":"AAPL","tdate":"2013-11-26","price":"533.40","VOL":"0.178087844824715"},{"ticker":"AAPL","tdate":"2013-11-27","price":"545.96","VOL":"0.187987250653775"},{"ticker":"AAPL","tdate":"2013-11-29","price":"556.07","VOL":"0.19291247343686"},{"ticker":"AAPL","tdate":"2013-12-02","price":"551.23","VOL":"0.195392658373822"},{"ticker":"AAPL","tdate":"2013-12-03","price":"566.32","VOL":"0.198211747949849"},{"ticker":"AAPL","tdate":"2013-12-04","price":"565.00","VOL":"0.19808360298352"},{"ticker":"AAPL","tdate":"2013-12-05","price":"567.90","VOL":"0.19716095745469"},{"ticker":"AAPL","tdate":"2013-12-06","price":"560.02","VOL":"0.200448611372548"},{"ticker":"AAPL","tdate":"2013-12-09","price":"566.43","VOL":"0.198263046444197"},{"ticker":"AAPL","tdate":"2013-12-10","price":"565.55","VOL":"0.198100660818933"},{"ticker":"AAPL","tdate":"2013-12-11","price":"561.36","VOL":"0.182686310947391"},{"ticker":"AAPL","tdate":"2013-12-12","price":"560.54","VOL":"0.178968180571978"},{"ticker":"AAPL","tdate":"2013-12-13","price":"554.43","VOL":"0.182177169130701"},{"ticker":"AAPL","tdate":"2013-12-16","price":"557.50","VOL":"0.181086060938817"},{"ticker":"AAPL","tdate":"2013-12-17","price":"554.99","VOL":"0.179464900321255"},{"ticker":"AAPL","tdate":"2013-12-18","price":"550.77","VOL":"0.181182396659916"},{"ticker":"AAPL","tdate":"2013-12-19","price":"544.46","VOL":"0.184811075666558"},{"ticker":"AAPL","tdate":"2013-12-20","price":"549.02","VOL":"0.177873957920341"},{"ticker":"AAPL","tdate":"2013-12-23","price":"570.09","VOL":"0.202045192617558"},{"ticker":"AAPL","tdate":"2013-12-24","price":"567.67","VOL":"0.202426988470211"},{"ticker":"AAPL","tdate":"2013-12-26","price":"563.90","VOL":"0.204336339708499"},{"ticker":"AAPL","tdate":"2013-12-27","price":"560.09","VOL":"0.206145940389823"},{"ticker":"FB","tdate":"2013-09-30","price":"50.23","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-01","price":"50.42","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-02","price":"50.28","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-03","price":"49.18","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-04","price":"51.04","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-07","price":"50.52","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-08","price":"47.14","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-09","price":"46.77","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-10","price":"49.05","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-11","price":"49.11","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-14","price":"49.51","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-15","price":"49.50","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-16","price":"51.14","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-17","price":"52.21","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-18","price":"54.22","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-21","price":"53.85","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-22","price":"52.68","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-23","price":"51.90","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-24","price":"52.45","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-25","price":"51.95","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-28","price":"50.23","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-29","price":"49.40","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-30","price":"49.01","VOL":"NULL"},{"ticker":"FB","tdate":"2013-10-31","price":"50.21","VOL":"NULL"},{"ticker":"FB","tdate":"2013-11-01","price":"49.75","VOL":"NULL"},{"ticker":"FB","tdate":"2013-11-04","price":"48.22","VOL":"NULL"},{"ticker":"FB","tdate":"2013-11-05","price":"50.11","VOL":"NULL"},{"ticker":"FB","tdate":"2013-11-06","price":"49.12","VOL":"NULL"},{"ticker":"FB","tdate":"2013-11-07","price":"47.56","VOL":"NULL"},{"ticker":"FB","tdate":"2013-11-08","price":"47.53","VOL":"NULL"},{"ticker":"FB","tdate":"2013-11-11","price":"46.20","VOL":"0.413408370202952"},{"ticker":"FB","tdate":"2013-11-12","price":"46.61","VOL":"0.414365401035481"},{"ticker":"FB","tdate":"2013-11-13","price":"48.71","VOL":"0.435894949700045"},{"ticker":"FB","tdate":"2013-11-14","price":"48.99","VOL":"0.431653934954346"},{"ticker":"FB","tdate":"2013-11-15","price":"49.01","VOL":"0.416987639366383"},{"ticker":"FB","tdate":"2013-11-18","price":"45.83","VOL":"0.458041915373724"},{"ticker":"FB","tdate":"2013-11-19","price":"46.36","VOL":"0.414670400399728"},{"ticker":"FB","tdate":"2013-11-20","price":"46.43","VOL":"0.414121926202439"},{"ticker":"FB","tdate":"2013-11-21","price":"46.70","VOL":"0.389126146601151"},{"ticker":"FB","tdate":"2013-11-22","price":"46.23","VOL":"0.389789146687931"},{"ticker":"FB","tdate":"2013-11-25","price":"44.82","VOL":"0.397353542073473"},{"ticker":"FB","tdate":"2013-11-26","price":"45.89","VOL":"0.404887818224236"},{"ticker":"FB","tdate":"2013-11-27","price":"46.49","VOL":"0.393950906964857"},{"ticker":"FB","tdate":"2013-11-29","price":"47.01","VOL":"0.389859680209728"},{"ticker":"FB","tdate":"2013-12-02","price":"47.06","VOL":"0.370105984913441"},{"ticker":"FB","tdate":"2013-12-03","price":"46.73","VOL":"0.370115858530058"},{"ticker":"FB","tdate":"2013-12-04","price":"48.62","VOL":"0.387834189850461"},{"ticker":"FB","tdate":"2013-12-05","price":"48.34","VOL":"0.386228074606856"},{"ticker":"FB","tdate":"2013-12-06","price":"47.94","VOL":"0.384613467723489"},{"ticker":"FB","tdate":"2013-12-09","price":"48.84","VOL":"0.389068143912512"},{"ticker":"FB","tdate":"2013-12-10","price":"50.25","VOL":"0.386866728808467"},{"ticker":"FB","tdate":"2013-12-11","price":"49.38","VOL":"0.387174385541323"},{"ticker":"FB","tdate":"2013-12-12","price":"51.83","VOL":"0.410886254243779"},{"ticker":"FB","tdate":"2013-12-13","price":"53.32","VOL":"0.413017561316782"},{"ticker":"FB","tdate":"2013-12-16","price":"53.81","VOL":"0.412114218265737"},{"ticker":"FB","tdate":"2013-12-17","price":"54.86","VOL":"0.40195314573859"},{"ticker":"FB","tdate":"2013-12-18","price":"55.57","VOL":"0.389719506456651"},{"ticker":"FB","tdate":"2013-12-19","price":"55.05","VOL":"0.385389246215378"},{"ticker":"FB","tdate":"2013-12-20","price":"55.12","VOL":"0.370063133368606"},{"ticker":"FB","tdate":"2013-12-23","price":"57.77","VOL":"0.389076800308892"},{"ticker":"FB","tdate":"2013-12-24","price":"57.96","VOL":"0.374973997553309"},{"ticker":"FB","tdate":"2013-12-26","price":"57.73","VOL":"0.376430695610467"},{"ticker":"FB","tdate":"2013-12-27","price":"55.44","VOL":"0.384016812079829"},{"ticker":"IBM","tdate":"2013-09-30","price":"184.19","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-01","price":"185.38","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-02","price":"183.97","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-03","price":"182.88","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-04","price":"183.12","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-07","price":"181.04","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-08","price":"177.77","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-09","price":"180.35","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-10","price":"183.78","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-11","price":"185.17","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-14","price":"185.97","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-15","price":"183.67","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-16","price":"185.73","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-17","price":"173.90","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-18","price":"172.85","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-21","price":"171.94","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-22","price":"174.04","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-23","price":"174.83","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-24","price":"176.85","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-25","price":"175.91","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-28","price":"176.40","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-29","price":"181.15","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-30","price":"179.19","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-10-31","price":"178.25","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-11-01","price":"178.27","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-11-04","price":"179.31","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-11-05","price":"176.90","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-11-06","price":"179.19","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-11-07","price":"180.00","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-11-08","price":"179.99","VOL":"NULL"},{"ticker":"IBM","tdate":"2013-11-11","price":"182.88","VOL":"0.259600756641362"},{"ticker":"IBM","tdate":"2013-11-12","price":"183.07","VOL":"0.25886435068979"},{"ticker":"IBM","tdate":"2013-11-13","price":"183.55","VOL":"0.258084881217436"},{"ticker":"IBM","tdate":"2013-11-14","price":"182.21","VOL":"0.25838941695409"},{"ticker":"IBM","tdate":"2013-11-15","price":"183.19","VOL":"0.258851361662631"},{"ticker":"IBM","tdate":"2013-11-18","price":"184.47","VOL":"0.257272874238383"},{"ticker":"IBM","tdate":"2013-11-19","price":"185.25","VOL":"0.25113141270427"},{"ticker":"IBM","tdate":"2013-11-20","price":"185.19","VOL":"0.248098166203494"},{"ticker":"IBM","tdate":"2013-11-21","price":"184.13","VOL":"0.242810179393185"},{"ticker":"IBM","tdate":"2013-11-22","price":"181.30","VOL":"0.245804723406176"},{"ticker":"IBM","tdate":"2013-11-25","price":"178.94","VOL":"0.247889849528801"},{"ticker":"IBM","tdate":"2013-11-26","price":"177.31","VOL":"0.246782618938391"},{"ticker":"IBM","tdate":"2013-11-27","price":"178.97","VOL":"0.24604234701296"},{"ticker":"IBM","tdate":"2013-11-29","price":"179.68","VOL":"0.152062274334431"},{"ticker":"IBM","tdate":"2013-12-02","price":"177.48","VOL":"0.155660954549234"},{"ticker":"IBM","tdate":"2013-12-03","price":"176.08","VOL":"0.156753405261162"},{"ticker":"IBM","tdate":"2013-12-04","price":"175.74","VOL":"0.153166929494441"},{"ticker":"IBM","tdate":"2013-12-05","price":"176.08","VOL":"0.152731802077581"},{"ticker":"IBM","tdate":"2013-12-06","price":"177.67","VOL":"0.151297530686739"},{"ticker":"IBM","tdate":"2013-12-09","price":"177.46","VOL":"0.15046658789529"},{"ticker":"IBM","tdate":"2013-12-10","price":"177.12","VOL":"0.15040745529138"},{"ticker":"IBM","tdate":"2013-12-11","price":"175.20","VOL":"0.131153490377389"},{"ticker":"IBM","tdate":"2013-12-12","price":"173.37","VOL":"0.13091290987267"},{"ticker":"IBM","tdate":"2013-12-13","price":"172.80","VOL":"0.130493430608268"},{"ticker":"IBM","tdate":"2013-12-16","price":"177.85","VOL":"0.15657740328222"},{"ticker":"IBM","tdate":"2013-12-17","price":"175.76","VOL":"0.159130504552285"},{"ticker":"IBM","tdate":"2013-12-18","price":"178.70","VOL":"0.161891633259364"},{"ticker":"IBM","tdate":"2013-12-19","price":"180.22","VOL":"0.159420520640619"},{"ticker":"IBM","tdate":"2013-12-20","price":"180.02","VOL":"0.158928812684109"},{"ticker":"IBM","tdate":"2013-12-23","price":"182.23","VOL":"0.162812027777703"},{"ticker":"IBM","tdate":"2013-12-24","price":"183.22","VOL":"0.156848086287117"},{"ticker":"IBM","tdate":"2013-12-26","price":"185.35","VOL":"0.160341722727633"},{"ticker":"IBM","tdate":"2013-12-27","price":"185.08","VOL":"0.160289637318093"},{"ticker":"MSFT","tdate":"2013-09-30","price":"33.03","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-01","price":"33.33","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-02","price":"33.66","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-03","price":"33.61","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-04","price":"33.62","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-07","price":"33.05","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-08","price":"32.76","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-09","price":"32.82","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-10","price":"33.51","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-11","price":"33.87","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-14","price":"34.19","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-15","price":"34.23","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-16","price":"34.38","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-17","price":"34.66","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-18","price":"34.70","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-21","price":"34.73","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-22","price":"34.32","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-23","price":"33.51","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-24","price":"33.47","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-25","price":"35.46","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-28","price":"35.30","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-29","price":"35.25","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-30","price":"35.27","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-10-31","price":"35.14","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-11-01","price":"35.26","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-11-04","price":"35.67","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-11-05","price":"36.36","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-11-06","price":"37.89","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-11-07","price":"37.22","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-11-08","price":"37.50","VOL":"NULL"},{"ticker":"MSFT","tdate":"2013-11-11","price":"37.31","VOL":"0.254950380754112"},{"ticker":"MSFT","tdate":"2013-11-12","price":"37.08","VOL":"0.256181821008676"},{"ticker":"MSFT","tdate":"2013-11-13","price":"37.87","VOL":"0.260609811856037"},{"ticker":"MSFT","tdate":"2013-11-14","price":"37.73","VOL":"0.261088903767016"},{"ticker":"MSFT","tdate":"2013-11-15","price":"37.56","VOL":"0.262029725657673"},{"ticker":"MSFT","tdate":"2013-11-18","price":"36.92","VOL":"0.262089613468521"},{"ticker":"MSFT","tdate":"2013-11-19","price":"36.74","VOL":"0.26070530174271"},{"ticker":"MSFT","tdate":"2013-11-20","price":"37.08","VOL":"0.261092730265093"},{"ticker":"MSFT","tdate":"2013-11-21","price":"37.40","VOL":"0.256650902151733"},{"ticker":"MSFT","tdate":"2013-11-22","price":"37.57","VOL":"0.255805679120139"},{"ticker":"MSFT","tdate":"2013-11-25","price":"37.64","VOL":"0.255215135927795"},{"ticker":"MSFT","tdate":"2013-11-26","price":"37.35","VOL":"0.257129548246975"},{"ticker":"MSFT","tdate":"2013-11-27","price":"37.60","VOL":"0.257329564017454"},{"ticker":"MSFT","tdate":"2013-11-29","price":"38.13","VOL":"0.258909236195725"},{"ticker":"MSFT","tdate":"2013-12-02","price":"38.45","VOL":"0.259260740509458"},{"ticker":"MSFT","tdate":"2013-12-03","price":"38.31","VOL":"0.259976144055881"},{"ticker":"MSFT","tdate":"2013-12-04","price":"38.94","VOL":"0.25853796720115"},{"ticker":"MSFT","tdate":"2013-12-05","price":"38.00","VOL":"0.259063106130924"},{"ticker":"MSFT","tdate":"2013-12-06","price":"38.36","VOL":"0.258973608663078"},{"ticker":"MSFT","tdate":"2013-12-09","price":"38.71","VOL":"0.204833360753929"},{"ticker":"MSFT","tdate":"2013-12-10","price":"38.11","VOL":"0.210778618295736"},{"ticker":"MSFT","tdate":"2013-12-11","price":"37.61","VOL":"0.215426672867916"},{"ticker":"MSFT","tdate":"2013-12-12","price":"37.22","VOL":"0.218466590217453"},{"ticker":"MSFT","tdate":"2013-12-13","price":"36.69","VOL":"0.222925338094543"},{"ticker":"MSFT","tdate":"2013-12-16","price":"36.89","VOL":"0.22315833165144"},{"ticker":"MSFT","tdate":"2013-12-17","price":"36.52","VOL":"0.223499790309627"},{"ticker":"MSFT","tdate":"2013-12-18","price":"36.58","VOL":"0.216646945703847"},{"ticker":"MSFT","tdate":"2013-12-19","price":"36.25","VOL":"0.179805278869911"},{"ticker":"MSFT","tdate":"2013-12-20","price":"36.80","VOL":"0.179064974608653"},{"ticker":"MSFT","tdate":"2013-12-23","price":"36.62","VOL":"0.177929915549888"},{"ticker":"MSFT","tdate":"2013-12-24","price":"37.08","VOL":"0.181497152287328"},{"ticker":"MSFT","tdate":"2013-12-26","price":"37.44","VOL":"0.182768111571843"},{"ticker":"MSFT","tdate":"2013-12-27","price":"37.29","VOL":"0.172163656133405"},{"ticker":"ORCL","tdate":"2013-09-30","price":"33.05","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-01","price":"33.38","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-02","price":"33.56","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-03","price":"33.12","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-04","price":"33.21","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-07","price":"32.84","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-08","price":"32.37","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-09","price":"32.19","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-10","price":"32.99","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-11","price":"33.26","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-14","price":"33.28","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-15","price":"32.75","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-16","price":"33.02","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-17","price":"32.87","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-18","price":"32.90","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-21","price":"32.95","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-22","price":"32.90","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-23","price":"32.70","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-24","price":"33.07","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-25","price":"33.15","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-28","price":"33.14","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-29","price":"33.71","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-30","price":"33.53","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-10-31","price":"33.50","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-11-01","price":"33.53","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-11-04","price":"33.71","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-11-05","price":"33.50","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-11-06","price":"34.07","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-11-07","price":"34.00","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-11-08","price":"34.35","VOL":"NULL"},{"ticker":"ORCL","tdate":"2013-11-11","price":"34.37","VOL":"0.150189552419081"},{"ticker":"ORCL","tdate":"2013-11-12","price":"34.70","VOL":"0.150003854614319"},{"ticker":"ORCL","tdate":"2013-11-13","price":"35.00","VOL":"0.151056836715572"},{"ticker":"ORCL","tdate":"2013-11-14","price":"34.38","VOL":"0.155524872821249"},{"ticker":"ORCL","tdate":"2013-11-15","price":"34.92","VOL":"0.160960193098425"},{"ticker":"ORCL","tdate":"2013-11-18","price":"34.93","VOL":"0.156351173876592"},{"ticker":"ORCL","tdate":"2013-11-19","price":"34.76","VOL":"0.149932066659868"},{"ticker":"ORCL","tdate":"2013-11-20","price":"34.75","VOL":"0.148269358199017"},{"ticker":"ORCL","tdate":"2013-11-21","price":"34.94","VOL":"0.13321556186422"},{"ticker":"ORCL","tdate":"2013-11-22","price":"34.83","VOL":"0.132644327249953"},{"ticker":"ORCL","tdate":"2013-11-25","price":"34.78","VOL":"0.132900547285873"},{"ticker":"ORCL","tdate":"2013-11-26","price":"34.93","VOL":"0.122245849244905"},{"ticker":"ORCL","tdate":"2013-11-27","price":"35.29","VOL":"0.123265455440989"},{"ticker":"ORCL","tdate":"2013-11-29","price":"35.29","VOL":"0.121790241831743"},{"ticker":"ORCL","tdate":"2013-12-02","price":"35.08","VOL":"0.124115407570261"},{"ticker":"ORCL","tdate":"2013-12-03","price":"35.07","VOL":"0.124303653522474"},{"ticker":"ORCL","tdate":"2013-12-04","price":"35.07","VOL":"0.123999332335981"},{"ticker":"ORCL","tdate":"2013-12-05","price":"34.85","VOL":"0.124113192575353"},{"ticker":"ORCL","tdate":"2013-12-06","price":"35.48","VOL":"0.129747978388065"},{"ticker":"ORCL","tdate":"2013-12-09","price":"35.60","VOL":"0.129782418776215"},{"ticker":"ORCL","tdate":"2013-12-10","price":"34.80","VOL":"0.148701750006502"},{"ticker":"ORCL","tdate":"2013-12-11","price":"34.56","VOL":"0.143225579124709"},{"ticker":"ORCL","tdate":"2013-12-12","price":"33.60","VOL":"0.165344904639033"},{"ticker":"ORCL","tdate":"2013-12-13","price":"33.23","VOL":"0.168462795758679"},{"ticker":"ORCL","tdate":"2013-12-16","price":"33.54","VOL":"0.170707200458498"},{"ticker":"ORCL","tdate":"2013-12-17","price":"33.63","VOL":"0.170154787941637"},{"ticker":"ORCL","tdate":"2013-12-18","price":"34.60","VOL":"0.187985873953149"},{"ticker":"ORCL","tdate":"2013-12-19","price":"36.60","VOL":"0.243149504428376"},{"ticker":"ORCL","tdate":"2013-12-20","price":"36.37","VOL":"0.244133722672318"},{"ticker":"ORCL","tdate":"2013-12-23","price":"36.93","VOL":"0.245997226333116"},{"ticker":"ORCL","tdate":"2013-12-24","price":"37.32","VOL":"0.247034049568057"},{"ticker":"ORCL","tdate":"2013-12-26","price":"37.69","VOL":"0.247109898714265"},{"ticker":"ORCL","tdate":"2013-12-27","price":"37.98","VOL":"0.246930768592208"}]}
In this example, we use the same data as in the previous example, except that the data are no longer in 3rd normal form. The closing prices for each of the 5 tickers are now stored in columns with each each date containing a 5 columns, one for each ticker. We can use the @Id variable to calculate the 5 moving volatilities in a single select. Additionally, we will only return the non-null values.
SELECT *
FROM
(
SELECT tdate,
wct.MovingVOLATILITY(AAPL, 252, 30, ROW_NUMBER() OVER (ORDER BY tdate)
, 0, 'True') as AAPL,
wct.MovingVOLATILITY(FB, 252, 30, ROW_NUMBER() OVER (ORDER BY tdate),
1, 'True') as FB,
wct.MovingVOLATILITY(IBM, 252, 30, ROW_NUMBER() OVER (ORDER BY tdate),
2, 'True') as IBM,
wct.MovingVOLATILITY(MSFT, 252, 30, ROW_NUMBER() OVER (ORDER BY tdate)
, 3, 'True') as MSFT,
wct.MovingVOLATILITY(ORCL, 252, 30, ROW_NUMBER() OVER (ORDER BY tdate)
, 4, 'True') as ORCL
FROM
(
VALUES
('2013-09-30', 473.98, 50.23, 184.19, 33.03, 33.05),
('2013-10-01', 485.13, 50.42, 185.38, 33.33, 33.38),
('2013-10-02', 486.72, 50.28, 183.97, 33.66, 33.56),
('2013-10-03', 480.6, 49.18, 182.88, 33.61, 33.12),
('2013-10-04', 480.23, 51.04, 183.12, 33.62, 33.21),
('2013-10-07', 484.92, 50.52, 181.04, 33.05, 32.84),
('2013-10-08', 478.15, 47.14, 177.77, 32.76, 32.37),
('2013-10-09', 483.77, 46.77, 180.35, 32.82, 32.19),
('2013-10-10', 486.8, 49.05, 183.78, 33.51, 32.99),
('2013-10-11', 489.95, 49.11, 185.17, 33.87, 33.26),
('2013-10-14', 493.16, 49.51, 185.97, 34.19, 33.28),
('2013-10-15', 495.79, 49.5, 183.67, 34.23, 32.75),
('2013-10-16', 498.2, 51.14, 185.73, 34.38, 33.02),
('2013-10-17', 501.57, 52.21, 173.9, 34.66, 32.87),
('2013-10-18', 505.94, 54.22, 172.85, 34.7, 32.9),
('2013-10-21', 518.33, 53.85, 171.94, 34.73, 32.95),
('2013-10-22', 516.85, 52.68, 174.04, 34.32, 32.9),
('2013-10-23', 521.91, 51.9, 174.83, 33.51, 32.7),
('2013-10-24', 528.82, 52.45, 176.85, 33.47, 33.07),
('2013-10-25', 522.91, 51.95, 175.91, 35.46, 33.15),
('2013-10-28', 526.8, 50.23, 176.4, 35.3, 33.14),
('2013-10-29', 513.68, 49.4, 181.15, 35.25, 33.71),
('2013-10-30', 521.85, 49.01, 179.19, 35.27, 33.53),
('2013-10-31', 519.67, 50.21, 178.25, 35.14, 33.5),
('2013-11-01', 517.01, 49.75, 178.27, 35.26, 33.53),
('2013-11-04', 523.69, 48.22, 179.31, 35.67, 33.71),
('2013-11-05', 522.4, 50.11, 176.9, 36.36, 33.5),
('2013-11-06', 520.92, 49.12, 179.19, 37.89, 34.07),
('2013-11-07', 512.49, 47.56, 180, 37.22, 34),
('2013-11-08', 520.56, 47.53, 179.99, 37.5, 34.35),
('2013-11-11', 519.05, 46.2, 182.88, 37.31, 34.37),
('2013-11-12', 520.01, 46.61, 183.07, 37.08, 34.7),
('2013-11-13', 520.63, 48.71, 183.55, 37.87, 35),
('2013-11-14', 528.16, 48.99, 182.21, 37.73, 34.38),
('2013-11-15', 524.99, 49.01, 183.19, 37.56, 34.92),
('2013-11-18', 518.63, 45.83, 184.47, 36.92, 34.93),
('2013-11-19', 519.55, 46.36, 185.25, 36.74, 34.76),
('2013-11-20', 515, 46.43, 185.19, 37.08, 34.75),
('2013-11-21', 521.14, 46.7, 184.13, 37.4, 34.94),
('2013-11-22', 519.8, 46.23, 181.3, 37.57, 34.83),
('2013-11-25', 523.74, 44.82, 178.94, 37.64, 34.78),
('2013-11-26', 533.4, 45.89, 177.31, 37.35, 34.93),
('2013-11-27', 545.96, 46.49, 178.97, 37.6, 35.29),
('2013-11-29', 556.07, 47.01, 179.68, 38.13, 35.29),
('2013-12-02', 551.23, 47.06, 177.48, 38.45, 35.08),
('2013-12-03', 566.32, 46.73, 176.08, 38.31, 35.07),
('2013-12-04', 565, 48.62, 175.74, 38.94, 35.07),
('2013-12-05', 567.9, 48.34, 176.08, 38, 34.85),
('2013-12-06', 560.02, 47.94, 177.67, 38.36, 35.48),
('2013-12-09', 566.43, 48.84, 177.46, 38.71, 35.6),
('2013-12-10', 565.55, 50.25, 177.12, 38.11, 34.8),
('2013-12-11', 561.36, 49.38, 175.2, 37.61, 34.56),
('2013-12-12', 560.54, 51.83, 173.37, 37.22, 33.6),
('2013-12-13', 554.43, 53.32, 172.8, 36.69, 33.23),
('2013-12-16', 557.5, 53.81, 177.85, 36.89, 33.54),
('2013-12-17', 554.99, 54.86, 175.76, 36.52, 33.63),
('2013-12-18', 550.77, 55.57, 178.7, 36.58, 34.6),
('2013-12-19', 544.46, 55.05, 180.22, 36.25, 36.6),
('2013-12-20', 549.02, 55.12, 180.02, 36.8, 36.37),
('2013-12-23', 570.09, 57.77, 182.23, 36.62, 36.93),
('2013-12-24', 567.67, 57.96, 183.22, 37.08, 37.32),
('2013-12-26', 563.9, 57.73, 185.35, 37.44, 37.69),
('2013-12-27', 560.09, 55.44, 185.08, 37.29, 37.98)
) n (tdate, AAPL, FB, IBM, MSFT, ORCL)
) p
WHERE AAPL IS NOT NULL
AND FB IS NOT NULL
AND IBM IS NOT NULL
AND MSFT IS NOT NULL
AND ORCL IS NOT NULL;
This produces the following result.
{"columns":[{"field":"tdate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"AAPL","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"FB","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"IBM","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"MSFT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ORCL","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"tdate":"2013-11-11","AAPL":"0.182448898566812","FB":"0.413408370202952","IBM":"0.259600756641362","MSFT":"0.254950380754112","ORCL":"0.150189552419081"},{"tdate":"2013-11-12","AAPL":"0.172083895254781","FB":"0.414365401035481","IBM":"0.25886435068979","MSFT":"0.256181821008676","ORCL":"0.150003854614319"},{"tdate":"2013-11-13","AAPL":"0.172088922541504","FB":"0.435894949700045","IBM":"0.258084881217436","MSFT":"0.260609811856037","ORCL":"0.151056836715572"},{"tdate":"2013-11-14","AAPL":"0.169557381867762","FB":"0.431653934954346","IBM":"0.25838941695409","MSFT":"0.261088903767016","ORCL":"0.155524872821249"},{"tdate":"2013-11-15","AAPL":"0.171284793834041","FB":"0.416987639366383","IBM":"0.258851361662631","MSFT":"0.262029725657673","ORCL":"0.160960193098425"},{"tdate":"2013-11-18","AAPL":"0.175500927555936","FB":"0.458041915373724","IBM":"0.257272874238383","MSFT":"0.262089613468521","ORCL":"0.156351173876592"},{"tdate":"2013-11-19","AAPL":"0.168585936287561","FB":"0.414670400399728","IBM":"0.25113141270427","MSFT":"0.26070530174271","ORCL":"0.149932066659868"},{"tdate":"2013-11-20","AAPL":"0.169619590467246","FB":"0.414121926202439","IBM":"0.248098166203494","MSFT":"0.261092730265093","ORCL":"0.148269358199017"},{"tdate":"2013-11-21","AAPL":"0.17158174055262","FB":"0.389126146601151","IBM":"0.242810179393185","MSFT":"0.256650902151733","ORCL":"0.13321556186422"},{"tdate":"2013-11-22","AAPL":"0.171665850721973","FB":"0.389789146687931","IBM":"0.245804723406176","MSFT":"0.255805679120139","ORCL":"0.132644327249953"},{"tdate":"2013-11-25","AAPL":"0.171926757898195","FB":"0.397353542073473","IBM":"0.247889849528801","MSFT":"0.255215135927795","ORCL":"0.132900547285873"},{"tdate":"2013-11-26","AAPL":"0.178087844824715","FB":"0.404887818224236","IBM":"0.246782618938391","MSFT":"0.257129548246975","ORCL":"0.122245849244905"},{"tdate":"2013-11-27","AAPL":"0.187987250653775","FB":"0.393950906964857","IBM":"0.24604234701296","MSFT":"0.257329564017454","ORCL":"0.123265455440989"},{"tdate":"2013-11-29","AAPL":"0.19291247343686","FB":"0.389859680209728","IBM":"0.152062274334431","MSFT":"0.258909236195725","ORCL":"0.121790241831743"},{"tdate":"2013-12-02","AAPL":"0.195392658373822","FB":"0.370105984913441","IBM":"0.155660954549234","MSFT":"0.259260740509458","ORCL":"0.124115407570261"},{"tdate":"2013-12-03","AAPL":"0.198211747949849","FB":"0.370115858530058","IBM":"0.156753405261162","MSFT":"0.259976144055881","ORCL":"0.124303653522474"},{"tdate":"2013-12-04","AAPL":"0.19808360298352","FB":"0.387834189850461","IBM":"0.153166929494441","MSFT":"0.25853796720115","ORCL":"0.123999332335981"},{"tdate":"2013-12-05","AAPL":"0.19716095745469","FB":"0.386228074606856","IBM":"0.152731802077581","MSFT":"0.259063106130924","ORCL":"0.124113192575353"},{"tdate":"2013-12-06","AAPL":"0.200448611372548","FB":"0.384613467723489","IBM":"0.151297530686739","MSFT":"0.258973608663078","ORCL":"0.129747978388065"},{"tdate":"2013-12-09","AAPL":"0.198263046444197","FB":"0.389068143912512","IBM":"0.15046658789529","MSFT":"0.204833360753929","ORCL":"0.129782418776215"},{"tdate":"2013-12-10","AAPL":"0.198100660818933","FB":"0.386866728808467","IBM":"0.15040745529138","MSFT":"0.210778618295736","ORCL":"0.148701750006502"},{"tdate":"2013-12-11","AAPL":"0.182686310947391","FB":"0.387174385541323","IBM":"0.131153490377389","MSFT":"0.215426672867916","ORCL":"0.143225579124709"},{"tdate":"2013-12-12","AAPL":"0.178968180571978","FB":"0.410886254243779","IBM":"0.13091290987267","MSFT":"0.218466590217453","ORCL":"0.165344904639033"},{"tdate":"2013-12-13","AAPL":"0.182177169130701","FB":"0.413017561316782","IBM":"0.130493430608268","MSFT":"0.222925338094543","ORCL":"0.168462795758679"},{"tdate":"2013-12-16","AAPL":"0.181086060938817","FB":"0.412114218265737","IBM":"0.15657740328222","MSFT":"0.22315833165144","ORCL":"0.170707200458498"},{"tdate":"2013-12-17","AAPL":"0.179464900321255","FB":"0.40195314573859","IBM":"0.159130504552285","MSFT":"0.223499790309627","ORCL":"0.170154787941637"},{"tdate":"2013-12-18","AAPL":"0.181182396659916","FB":"0.389719506456651","IBM":"0.161891633259364","MSFT":"0.216646945703847","ORCL":"0.187985873953149"},{"tdate":"2013-12-19","AAPL":"0.184811075666558","FB":"0.385389246215378","IBM":"0.159420520640619","MSFT":"0.179805278869911","ORCL":"0.243149504428376"},{"tdate":"2013-12-20","AAPL":"0.177873957920341","FB":"0.370063133368606","IBM":"0.158928812684109","MSFT":"0.179064974608653","ORCL":"0.244133722672318"},{"tdate":"2013-12-23","AAPL":"0.202045192617558","FB":"0.389076800308892","IBM":"0.162812027777703","MSFT":"0.177929915549888","ORCL":"0.245997226333116"},{"tdate":"2013-12-24","AAPL":"0.202426988470211","FB":"0.374973997553309","IBM":"0.156848086287117","MSFT":"0.181497152287328","ORCL":"0.247034049568057"},{"tdate":"2013-12-26","AAPL":"0.204336339708499","FB":"0.376430695610467","IBM":"0.160341722727633","MSFT":"0.182768111571843","ORCL":"0.247109898714265"},{"tdate":"2013-12-27","AAPL":"0.206145940389823","FB":"0.384016812079829","IBM":"0.160289637318093","MSFT":"0.172163656133405","ORCL":"0.246930768592208"}]}