Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
Genuine reviews from real traders, not fake reviews from stealth vendors
Quality education from leading professional traders
We are a friendly, helpful, and positive community
We do not tolerate rude behavior, trolling, or vendors advertising in posts
We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community. It's free for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
Using Excel and learning VBA and R for Trading and Analysis
I don't have much time to trade these days so I thought I'd start learning to do some analysis, work on my fundamental knowledge and look at ways to utilise and later implement important data. A journal might be a bit overboard, but I'm hoping to make a collection of spreadsheets and hopefully some more advanced bits in the future, whilst giving instructions on how I went about it.
I will be using Bloomberg and Sierra for collecting data and initially analysing it in excel. I'd also welcome people joining in and adding their own stuff, especially for other platforms that I don't mention/use so that this can be useful for all futures.io (formerly BMT) users.
I'm decent at using excel but no means a whizz. I am planning to change this though and will be learning to code in VBA using the following youtube tutorials as a starting ground:
I have also enrolled into a free online R Programming course on coursera which is taught by John Hopkins Uni and will commence on the 5th October.
The first chart I created tonight was fairly simple. I spent an embarrassingly long time trying to get two lines to cross without the chart being too skewed due to the large difference between price and storage volume. I eventually found that you need to: 'Right click on chart > Format Plot Area > Series Options > Series Storage Volume > Secondary Axis' in order to use a second set of data points on the Y axis.
The data I used was pulled from Bloomberg. I searched for CL, then used the 'HP' function to pull the historical close price data and exported to excel. I then used 'NRGZ' to get the IEA reports data and chose DOE data for Crude Oil. The shortcut is 'DOESCRUD' then once again run 'HP'.
As the historical prices came as daily information but the DOE report came as a weekly data set, I then compiled the data and used a VLOOKUP function to pull in the price to the corresponding date. I have attached the excel sheet so other beginners can have a look. The end result shows that over the past year, whilst storage inventories have risen, the price of oil has fallen. I would say though that we need more data as from August 2014 (when the data starts) the price of CL was already falling whilst inventories were moving lower slightly too until about October 2014.
I then set about looking at some longer term data. This time I used weekly data both data sets so that they both matched. I pulled a 5 year report and plotted the data to another chart:
This is a little more useful. Initially the storage volume moves quite well with price, as volume increases, price moves lower and vice versa. I have highlighted areas with confluence (green boxes) and then areas where confluence is broken (red boxes). I think we really start to see a breakdown in confluence around September 2014 where price begins to fall despite volumes lowering too. This would suggest that there are additional factors causing price to decline (we know now it had to do with several factors including US Shale Oil boom).
Alone this is not too useful except for viewing the data in a simple, easy to register way, however with more data sets and additional information, these type of information could be very useful in providing a bigger picture analysis, especially if automatically updated on a regular basis.
