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 InputTable PARTITION BY id ORDER BY period USING TargetColumns ('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 predict_expenditure predict_income predict_investment -- ------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------- ------------------- ------------------ ------------------ 1 ma_params [[1.248390739091949, -1.265426674704899, 1.8366448514566578],[-0.04357992971257406, -0.8104294171113546, 3.1912081090547098],[0.5282557132572212, -0.8969134194646912, 0.6941781537982473]] NULL NULL NULL NULL 1 aic 25.8626571338923 NULL NULL NULL NULL 1 converged false NULL NULL NULL NULL 1 seasonal_ar_params NULL NULL NULL NULL NULL 1 sigma [[10937.698750122627, 10097.09183807659, 3520.1342920478005],[10097.09183807659, 14134.11499016197, 2999.6089815260793],[3520.1342920478005, 2999.6089815260793, 2433.4777442199834]] NULL NULL NULL NULL 1 period 0 NULL NULL NULL NULL 1 bic 26.630454816721365 NULL NULL NULL NULL 1 NULL NULL 3 1753.0780834153084 2213.770938403275 743.8238313279019 1 NULL NULL 1 1870.2700856952113 2220.305114150415 732.6964571772069 1 coef 1, 1, 1, 0, 0, 0, 0 NULL NULL NULL NULL 1 seasonal_ma_params NULL NULL NULL NULL NULL 1 exogenous_params NULL NULL NULL NULL NULL 1 iterations 100 NULL NULL NULL NULL 1 NULL NULL 2 1994.0913688056626 2240.692404271746 749.3573689292496 1 lag 0 NULL NULL NULL NULL 1 ar_params [[-2.342501109086588, 1.9423693188736098, 0.5665092606109724],[-0.23679729916046047, 0.463259213827403, -0.4228772439268348],[-0.05041900311204333, 0.3456632515726913, -0.3803950846917263]] NULL NULL NULL NULL 2 ma_params [[-1.7350580320377782, 2.253563262977889, 1.3630071115741689],[-2.5529132696801202, 2.8604609381524764, 0.8539594322792532],[-1.0527526199458779, 0.9124434365333087, 0.8251886996083113]] NULL NULL NULL NULL 2 seasonal_ar_params NULL NULL NULL NULL NULL 2 seasonal_ma_params NULL NULL NULL NULL NULL 2 period 0 NULL NULL NULL NULL 2 sigma [[13901.648209349993, 14915.088344538926, 3504.4514805844747],[14915.088344538926, 20430.147818275578, 5617.087770107875],[3504.4514805844747, 5617.087770107875, 2572.3863667164956]] NULL NULL NULL NULL 2 aic 25.67191854988387 NULL NULL NULL NULL 2 bic 26.439716232712936 NULL NULL NULL NULL 2 iterations 100 NULL NULL NULL NULL 2 converged false NULL NULL NULL NULL 2 NULL NULL 1 1610.2071609154068 1959.9484516211046 615.9731282343708 2 NULL NULL 2 1895.7603142841267 2113.7353384844932 562.4152008833979 2 NULL NULL 3 965.7973575959059 1398.1791635434158 645.9978584480411 2 lag 0 NULL NULL NULL NULL 2 exogenous_params NULL NULL NULL NULL NULL 2 ar_params [[-4.059896316264422, 2.184439535900078, 1.9900866644404875],[-2.136312518482259, 0.2996326943183063, 2.8306729828361092],[0.48777641750942646, -0.4954211191320447, -0.38249865177522036]] NULL NULL NULL NULL 2 coef 1, 1, 1, 0, 0, 0, 0 NULL NULL NULL NULL 3 NULL NULL 1 2291.751800546244 2771.519391960251 843.1553213951414 3 converged false NULL NULL NULL NULL 3 iterations 100 NULL NULL NULL NULL 3 aic 26.01717883870844 NULL NULL NULL NULL 3 sigma [[4675.639154179282, -271.81096013599335, 500.1394808930519],[-271.81096013599335, 1347.982467607846, 256.8154939947388],[500.1394808930519, 256.8154939947388, 1326.5247222046553]] NULL NULL NULL NULL 3 lag 0 NULL NULL NULL NULL 3 bic 26.668280194196683 NULL NULL NULL NULL 3 period 0 NULL NULL NULL NULL 3 seasonal_ma_params NULL NULL NULL NULL NULL 3 seasonal_ar_params NULL NULL NULL NULL NULL 3 exogenous_params NULL NULL NULL NULL NULL 3 NULL NULL 2 2256.0140486155283 3057.017727562449 1012.7661239880138 3 ma_params [[-0.6946133251302785, -0.21908174663171212, 2.0539095733809596],[-5.146412337538272, 1.8914154757069057, -0.023736634213482252],[-1.3446784229340576, -0.10492861623291512, 0.197379047992889]] NULL NULL NULL NULL 3 ar_params [[1.4735147057787517, -0.3714896584284457, -1.637673972878577],[-0.49114758180592777, 2.419163855654859, 0.31427398834899123],[-1.6601973673615065, 1.665300190890637, 0.25555596015840437]] NULL NULL NULL NULL 3 coef 1, 1, 1, 0, 0, 0, 0 NULL NULL NULL NULL 3 NULL NULL 3 1819.5270694940025 3818.5417557387937 1590.8833299459645
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.