This example uses three time series as response columns modeled by the autoregression (AR) and moving average (MA) parameters. Because the model uses no exogenous variables, it is equivalent to the VARMA model.
Input
The input table, finance_data3, has seasonally adjusted quarterly financial data from West Germany between 1960 and 1982, in billions of Deutsche Marks. The table has three time series—consumer expenditures, disposable income, and fixed investmen—partitioned by the column id, which indicates the decade.
id | period | expenditure | income | id | investment |
---|---|---|---|---|---|
1 | 1960Q1 | 415 | 451 | 1 | 180 |
1 | 1960Q2 | 421 | 465 | 1 | 179 |
1 | 1960Q3 | 434 | 485 | 1 | 185 |
1 | 1960Q4 | 448 | 493 | 1 | 192 |
1 | 1961Q1 | 459 | 509 | 1 | 211 |
1 | 1961Q2 | 458 | 520 | 1 | 202 |
1 | 1961Q3 | 479 | 521 | 1 | 207 |
1 | 1961Q4 | 487 | 540 | 1 | 214 |
1 | 1962Q1 | 497 | 548 | 1 | 231 |
1 | 1962Q2 | 510 | 558 | 1 | 229 |
1 | 1962Q3 | 516 | 574 | 1 | 234 |
1 | 1962Q4 | 525 | 583 | 1 | 237 |
1 | 1963Q1 | 529 | 591 | 1 | 206 |
1 | 1963Q2 | 538 | 599 | 1 | 250 |
... | ... | ... | ... | ... | ... |
SQL Call
Three values are predicted (StepAhead (3)) for each time series with PDQ (1, 1, 1).
SELECT * FROM VARMAX ( ON finance_data3 AS "data" PARTITION BY id ORDER BY period USING ResponseColumns ('expenditure', 'income', 'investment') PartitionColumns ('id') PDQ ('1, 1, 1') IncludeMean ('false') StepAhead (3) ) AS dt ORDER BY id;
Output
Series id = 2 does not converge. You might improve convergence by adding more orders or more models.
id | coef | coef_value | stepahead | id | predict_expenditure | predict_income | predict_investment |
---|---|---|---|---|---|---|---|
1 | coef | 1, 1, 1, 0, 0, 0, 0 | 1 | ||||
1 | ar_params | [[0.5401142443401908, -0.22144696209085052, -0.8658134069625404], [0.46112845524514406, -0.3141400396767603, 0.5401312499458767], [-0.5436170410196307, 0.3550487442187296, 0.11840800979913536]] | 1 | ||||
1 | ma_params | [[0.9418274466713232, 0.47136752532237564, -3.5912652252354267], [0.6921921561542808, 1.1978201256969616, -3.1537918114389636], [-0.4072848688634932, 0.5456117174031574, -1.0037912602351198]] | 1 | ||||
1 | exogenous_params | 1 | |||||
1 | seasonal_ar_params | 1 | |||||
1 | seasonal_ma_params | 1 | |||||
1 | period | 0 | 1 | ||||
1 | lag | 0 | 1 | ||||
1 | sigma | [[9303.34637558586, 8911.919784323425, 2489.9006735815274], [8911.919784323425, 11603.406279896304, 2608.8269629048896], [2489.9006735815274, 2608.8269629048896, 1873.3459372621583]] | 1 | ||||
1 | aic | 25.17173031402279 | 1 | ||||
1 | bic | 25.939527996851854 | 1 | ||||
1 | iterations | 90 | 1 | ||||
1 | converged | true | 1 | ||||
1 | 1 | 1 | 1938.76079858415 | 2252.24766189307 | 744.997964522574 | ||
1 | 2 | 1 | 1925.43436379877 | 2283.39702101694 | 740.419046284062 | ||
1 | 3 | 1 | 1915.30312439615 | 2264.99334498778 | 758.180883573926 | ||
2 | coef | 1, 1, 1, 0, 0, 0, 0 | 2 | ||||
2 | ar_params | [[-2.068628487877914, 1.4352819442850229, 0.5040312926651062], [-1.7033511912858643, 1.4975506746058223, 0.5074485754476421], [0.02390658218391805, 0.09069981493533767, 0.6323254473495666]] | 2 | ||||
2 | ma_params | [[1.1099224166373396, -1.4699745679694927, -0.13597198353294757], [2.0484118200773467, -1.461011739772329, -0.966987021587989], [1.215189995141357, -0.48167519587064467, -0.7805184850752649]] | 2 | ||||
2 | exogenous_params | 2 | |||||
2 | seasonal_ar_params | 2 | |||||
2 | seasonal_ma_params | 2 | |||||
2 | period | 0 | 2 | ||||
2 | lag | 0 | 2 | ||||
2 | sigma | [[20710.85970757356, 19488.49092866746, 3238.838942282427], [19488.49092866746, 23099.4579527159, 7096.235865937958], [3238.838942282427, 7096.235865937958, 4997.15664809357]] | 2 | ||||
2 | aic | 26.28445013189386 | 2 | ||||
2 | bic | 27.052247814722925 | 2 | ||||
2 | iterations | 100 | 2 | ||||
2 | converged | false | 2 | ||||
2 | 1 | 2 | 1647.53805974095 | 1992.4468446864 | 677.020182217684 | ||
2 | 2 | 2 | 1797.20517373109 | 2154.41616652847 | 744.891590184598 | ||
2 | 3 | 2 | 1754.28047469318 | 2176.47902609351 | 806.077025266635 | ||
3 | coef | 1, 1, 1, 0, 0, 0, 0 | 3 | ||||
3 | ar_params | [[2.356733508164794, -0.5621044042754211, -1.4440215700897763], [1.3690594672816452, -0.374171671178873, 0.145908702245906], [-0.46084660401307126, 0.5119208015206022, 0.5487744352179722]] | 3 | ||||
3 | ma_params | [[1.4346653216242653, 1.2117554668497033, -1.3437736962000293], [2.4823004009827208, 0.7810702841064198, 0.9249544231941549], [1.2614528273336505, 0.46230687456647834, 0.8258407892382512]] | 3 | ||||
3 | exogenous_params | 3 | |||||
3 | seasonal_ar_params | 3 | |||||
3 | seasonal_ma_params | 3 | |||||
3 | period | 0 | 3 | ||||
3 | lag | 0 | 3 | ||||
3 | sigma | [[3350.0872240469225, 2079.903284457588, -57.80956553330401], [2079.903284457588, 1813.642959968383, 53.094810167970195], [-57.80956553330401, 53.094810167970195, 545.3242237694398]] | 3 | ||||
3 | aic | 23.919079900514713 | 3 | ||||
3 | bic | 24.570181256002957 | 3 | ||||
3 | iterations | 96 | 3 | ||||
3 | converged | true | 3 | ||||
3 | 1 | 3 | 2349.23362243522 | 2735.7962811932 | 863.755002444903 | ||
3 | 2 | 3 | 2437.20210713965 | 2816.0995450051 | 889.634165862864 | ||
3 | 3 | 3 | 2562.01149424731 | 2910.26242054004 | 904.404922890561 |