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.
Download Spreadsheet to Automatically Import WTI Oil Price History to Excel
Hi Samir,
This does not work and I’m very interested in it. Could you please check why (Tried it on number of PCs using Excel 2013 – does not work). Gives VBA error
Hi Ali
The old data source I was using was deprecated. I’ve just updated the spreadsheet with another data source. Download the updated spreadsheet and all should be good. Works for me on Excel 2013.
Samir
Hi Samir,
Can you provide this in weekly series or how can I change it to a weekly series ?
Thanks for all the work done.
there are some days thay WTI price is zero. Please help me explain it. Thank you very much!