Commodities¶

This example uses the commodities dataset to predict the price of different commodities. You can download the Jupyter Notebook of the study here.

date: Date of the record.
Gold: Price per ounce of Gold.
Oil: Price per Barrel - West Texas Intermediate (WTI).
Spread: Interest Rate Spreads.
Vix: The CBOE Volatility Index (VIX) is a measure of expected price fluctuations in the SP500 Index options over the next 30 - days.
Dol_Eur: How much $1 US is in euros.
SP500: The S&P 500, or simply the S&P, is a stock market index that measures the stock performance of 500 large companies - listed on stock exchanges in the United States.

We will follow the data science cycle (Data Exploration - Data Preparation - Data Modeling - Model Evaluation - Model Deployment) to solve this problem.

Initialization¶

This example uses the following version of VerticaPy:

import verticapy as vp

vp.__version__
Out[2]: '1.1.0'

Connect to Vertica. This example uses an existing connection called VerticaDSN. For details on how to create a connection, see the Connection tutorial. You can skip the below cell if you already have an established connection.

vp.connect("VerticaDSN")

Let’s create a Virtual DataFrame of the dataset.

from verticapy.datasets import load_commodities

commodities = load_commodities()
commodities.head(100)

	📅 date Date 100%	...	123 Dol_Eur Float 100%	123 SP500 Float 100%
1	1986-11-01	...	0.967399999999543	249.220001
2	1987-02-01	...	0.882600000000821	284.200012
3	1987-05-01	...	0.861100000000079	290.100006
4	1987-08-01	...	0.89600000000064	329.799988
5	1987-10-01	...	0.868599999999788	251.789993
6	1987-12-01	...	0.791400000000067	247.080002
7	1988-02-01	...	0.822000000000116	267.820007
8	1988-03-01	...	0.810900000000402	258.890015
9	1988-09-01	...	0.900299999999334	271.910004
10	1988-11-01	...	0.844499999999243	273.700012
11	1989-02-01	...	0.888899999999921	288.859985
12	1989-06-01	...	0.955400000000736	317.980011
13	1989-07-01	...	0.914000000000669	346.079987
14	1989-11-01	...	0.893899999999121	345.98999
15	1989-12-01	...	0.856499999999869	353.399994
16	1990-02-01	...	0.820799999999508	331.890015
17	1990-10-01	...	0.739799999999377	304.0
18	1991-01-01	...	0.736699999999473	343.929993
19	1991-04-01	...	0.826699999999619	375.339996
20	1991-05-01	...	0.83389999999963	389.829987
21	1992-03-01	...	0.812700000000404	403.690002
22	1992-05-01	...	0.78859999999986	415.350006
23	1992-07-01	...	0.729799999999159	424.209991
24	1992-10-01	...	0.755199999999604	418.679993
25	1992-11-01	...	0.807199999999284	431.350006
26	1992-12-01	...	0.807199999999284	435.709991
27	1993-03-01	...	0.848400000000766	451.670013
28	1993-04-01	...	0.820100000000821	440.190002
29	1993-05-01	...	0.821200000000317	450.190002
30	1993-06-01	...	0.844300000000658	450.529999
31	1993-08-01	...	0.882400000000416	463.559998
32	1994-04-01	...	0.877300000000105	450.910004
33	1994-06-01	...	0.845699999999852	444.269989
34	1994-07-01	...	0.818499999999404	458.26001
35	1995-07-01	...	0.743437999999514	562.059998
36	1996-03-01	...	0.780428570000368	645.5
37	1996-06-01	...	0.797715009999592	670.630005
38	1996-11-01	...	0.78265237999949	757.02002
39	1996-12-01	...	0.799684999999954	740.73999
40	1997-01-01	...	0.822170909999841	786.159973
41	1998-03-01	...	0.922254549999707	1101.75
42	1998-04-01	...	0.916540000000168	1111.75
43	1998-08-01	...	0.90782380952381	957.280029
44	1998-10-01	...	0.838318181818182	1098.670044
45	1999-07-01	...	0.966225199954545	1328.719971
46	1999-08-01	...	0.9435675375	1320.410034
47	2000-06-01	...	1.05298371918182	1454.599976
48	2000-09-01	...	1.14839744757143	1436.51001
49	2000-12-01	...	1.11084724065	1320.280029
50	2001-02-01	...	1.0853814393	1239.939941
51	2001-04-01	...	1.118874966	1249.459961
52	2001-05-01	...	1.143491682	1255.819946
53	2001-07-01	...	1.16093285659091	1211.22998
54	2001-09-01	...	1.09583135555	1040.939941
55	2002-08-01	...	1.02246676136364	916.070007
56	2002-10-01	...	1.01855763682609	885.76001
57	2003-05-01	...	0.864283957363636	963.590027
58	2003-09-01	...	0.888519456863636	995.969971
59	2004-05-01	...	0.832421512904762	1120.680054
60	2005-02-01	...	0.76765133505	1203.599976
61	2005-04-01	...	0.772679617285714	1156.849976
62	2005-08-01	...	0.813393210130435	1220.329956
63	2005-11-01	...	0.847904099363636	1249.47998
64	2006-05-01	...	0.782905714217391	1270.089966
65	2006-08-01	...	0.780600743304348	1303.819946
66	2006-09-01	...	0.785348418952381	1335.849976
67	2007-09-01	...	0.71884956405	1526.75
68	2007-12-01	...	0.68677322555	1468.359985
69	2008-03-01	...	0.644097590571429	1322.699951
70	2008-04-01	...	0.635092953636364	1385.589966
71	2008-05-01	...	0.642545454545455	1400.380005
72	2008-10-01	...	0.753347826086956	968.75
73	2009-07-01	...	0.709826086956522	987.47998
74	2009-11-01	...	0.670233333333333	1095.630005
75	2010-03-01	...	0.737	1169.430054
76	2010-05-01	...	0.795428571428571	1089.410034
77	2010-06-01	...	0.818954545454545	1030.709961
78	2010-09-01	...	0.763909090909091	1141.199951
79	2011-03-01	...	0.713565217391304	1325.829956
80	2011-04-01	...	0.691714285714286	1363.609985
81	2012-04-01	...	0.759952380952381	1397.910034
82	2012-08-01	...	0.806173913043478	1406.579956
83	2012-10-01	...	0.770608695652174	1412.160034
84	2013-01-01	...	0.752	1498.109985
85	2013-02-01	...	0.74955	1514.680054
86	2013-03-01	...	0.772047619047619	1569.189941
87	2013-05-01	...	0.770714285714286	1630.73999
88	2013-10-01	...	0.733005652173913	1756.540039
89	2014-02-01	...	0.731746	1859.449951
90	2014-04-01	...	0.724089090909091	1883.949951
91	2014-05-01	...	0.728108181818182	1923.569946
92	2014-08-01	...	0.75092	2003.369995
93	2014-10-01	...	0.789044347826087	2018.050049
94	2014-11-01	...	0.801368	2067.560059
95	2015-02-01	...	0.880563	2104.5
96	2015-05-01	...	0.896117142857143	2107.389893
97	2015-08-01	...	0.898364761904762	1972.180054
98	2015-09-01	...	0.891428636363636	1920.030029
99	2016-04-01	...	0.881817619047619	2065.300049
100	2016-11-01	...	0.927670454545454	2198.810059

Data Exploration and Preparation¶

Let’s explore the data by displaying descriptive statistics of all the columns.

commodities.describe(method = "all", unique = True)

	...	123 "SP500" Float 100%	📅 "date" Date 100%
dtype	...	float	date
percent	...	100	100
count	...	416	416
top	...	304.0	1986-01-01
top_percent	...	0.481	0.24
avg	...	1190.84896648798	[null]
stddev	...	752.215482101545	[null]
min	...	211.779999	1986-01-01
approx_25%	...	467.657494	[null]
approx_50%	...	1132.5	[null]
approx_75%	...	1454.767487	[null]
max	...	3500.310059	2020-08-01
range	...	3288.53006	12631
empty	...	[null]	[null]
unique	...	414.0	413.0

We have data from January 1986 to the beginning of August 2020. We don’t have any missing values, so our data is already clean.

Let’s draw the different variables.

commodities.plot(ts = "date")

Some of the commodities have an upward monotonic trend and some others might be stationary. Let’s use Augmented Dickey-Fuller tests to check our hypotheses.

from verticapy.machine_learning.model_selection.statistical_tests import adfuller
from verticapy.core.tablesample import TableSample

fuller = {}
for commodity in ["Gold", "Oil", "Spread", "Vix", "Dol_Eur", "SP500"]:
    result = adfuller(
        commodities,
        column = commodity,
        ts = "date",
        p = 3,
        with_trend = True,
    )
    fuller["index"] = result["index"]
    fuller[commodity] = result["value"]
fuller = TableSample(fuller)
fuller

	...	Dol_Eur	SP500
ADF Test Statistic	...	-2.7980161815599516	-0.09469625542100038
p_value	...	0.00538662270061962	0.924602810088177
# Lags used	...	3	3
# Observations Used	...	416	416
Critical Value (1%)	...	3.98	3.98
Critical Value (2.5%)	...	-3.68	-3.68
Critical Value (5%)	...	-3.42	-3.42
Critical Value (10%)	...	-3.13	-3.13
Stationarity (alpha = 1%)	...	✅	❌

As expected: The price of gold and the S&P 500 index are not stationary. Let’s use the Mann-Kendall test to confirm the trends.

from verticapy.machine_learning.model_selection.statistical_tests import mkt

kendall = {}
for commodity in ["Gold", "SP500"]:
    result = mkt(
        commodities,
        column = commodity,
        ts = "date",
    )
    kendall["index"] = result["index"]
    kendall[commodity] = result["value"]
kendall = TableSample(kendall)
kendall

	...	Gold	SP500
Mann Kendall Test Statistic	...	15.001075433725964	24.886617341458717
S	...	42504.0	70513.0
STDS	...	2833.33019607669	2833.33001960591
p_value	...	7.223928538831201e-51	1.0387121156452156e-136
Monotonic Trend	...	✅	✅
Trend	...	increasing	increasing

Our hypothesis is correct. We can also look at the correlation between the elapsed time and our variables to see the different trends.

import verticapy.sql.functions as fun

commodities["elapsed_days"] = commodities["date"] - fun.min(commodities["date"])._over()
commodities.corr(focus = "elapsed_days")

In the last plot, it’s a bit hard to tell if Spread is stationary. Let’s draw it alone.

commodities["Spread"].plot(ts = "date")

We can see some sudden changes, so let’s smooth the curve.

commodities.rolling(
    func = "avg",
    window = (-20, 0),
    columns = "Spread",
    order_by = ["date"],
    name = "Spread_smooth",
)
commodities["Spread_smooth"].plot(ts = "date")

After each local minimum, there is a local maximum. Let’s look at the number of lags needed to keep most of the information. To visualize this, we can draw the autocorrelation function (ACF) and partial autocorrelation function (PACF) plots.

commodities.acf(column = "Spread", ts = "date", p = 12)

commodities.pacf(column = "Spread", ts = "date", p = 5)

We can clearly see the influence of the last two values on Spread, which makes sense. When the curve slightly changes its direction, it will increase/decrease until reaching a new local maximum/minimum. Only the recent values can help the prediction in case of autoregressive periodical model. The local minimums of interest rate spreads are indicators of an economic crisis.

We saw the correlation between the price-per-barrel of Oil and the time. Let’s look at the time series plot of this variable.

commodities["Oil"].plot(ts = "date")

Moving on to the correlation matrix, we can see many events that changed drastically the values of commodities, and we know of a correlation between all of them. From here, we could look at how strong this correlation is, which will help us create a model that properly combines all the variable lags in its predictions.

commodities.corr(columns = ["Gold", "Oil", "Spread", "Vix", "Dol_Eur", "SP500"])

We can see strong correlations between most of the variables. A vector autoregression (VAR) model seems ideal.

Machine Learning¶

Let’s create the VAR model to predict the value of various commodities.

from verticapy.machine_learning.vertica import VAR

model = VAR(p = 5)
model.fit(
    commodities,
    ts = "date",
    y = ["Gold", "Oil", "Spread", "Vix", "Dol_Eur", "SP500"],
)
model.score()

	...	"Dol_Eur"	"SP500"
r2	...	0.677558766974035	0.942839597221071

Our model is excellent. Let’s predict the values these commodities in the near future.

Gold¶

model.plot(idx = 0, npredictions = 60)

Oil:¶

model.plot(idx = 1, npredictions = 60)

Spread:¶

model.plot(idx = 2, npredictions = 60)

Vix:¶

model.plot(idx = 3, npredictions = 60)

Dol_Eur:¶

model.plot(idx = 4, npredictions = 60)

The model performs well but may be somewhat unstable. To improve it, we could apply data preparation techniques, such as seasonal decomposition, before building the VAR model.

Conclusion¶

We’ve solved our problem in a Pandas-like way, all without ever loading data into memory!