Automatically download historical prices for West Texas Intermediate crude oil into Excel. Once in Excel, you can analyze, visualize and back-test the data.
Keen investors are usually interested in oil prices. This is because crude oil is part of a well-balanced portfolio, and you may already be investing in it via a commodities fund.
The correlation between oil and share prices changes over time. Historically, both have been negatively correlated.
However, during the financial crisis of 2008-2010 and the associated macro-economic shocks, oil and share prices became positively correlated.
West Texas Intermediate (or WTI) is one of several grades used when determining oil prices. Others include Brent, Dubai, OPEC and Isthmus). Analysts use historical oil prices for historical back-testing correlation analyses, momentum-based price indicators and more.
This spreadsheet isn’t complicated to use. Simply specify the start and end date on the “Parameters” sheet, and click the update button.
Some VBA downloads the historical monthly data into the spreadsheet, from your start date to your end date. The data is sourced from the US Energy Information Authority (EIA), and can be used your for own analyses.
You also get a plot of the historical oil prices, and the monthly percentage change in prices. These plots automatically refresh when you update the prices
The actual data is safely stored in another sheet called “Data”.
Just click the update button on the “Parameters” sheet to update the spreadsheet with the latest WTI prices.
You can easily extend the spreadsheet to calculate the historical volatility and relative strength index, plot moving averages, correlate oil and stock prices movements, and more. If you want to automate any of these analyses in VBA, then let me know.
You may also be interested in another spreadsheet that imports Brent oil price data.