Using Excel and learning VBA and R for Trading and Analysis - futures io
futures io



Using Excel and learning VBA and R for Trading and Analysis


Discussion in Trading Journals

Updated
    1. trending_up 1,304 views
    2. thumb_up 0 thanks given
    3. group 1 followers
    1. forum 3 posts
    2. attach_file 3 attachments




Welcome to futures io: the largest futures trading community on the planet, with well over 125,000 members
  • 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 and simple.

-- Big Mike, Site Administrator

(If you already have an account, login at the top of the page)

 
Search this Thread
 

Using Excel and learning VBA and R for Trading and Analysis

(login for full post details)
  #1 (permalink)
 CobblersAwls 
London, United Kingdom
 
Experience: Intermediate
Platform: N/A
Broker: Bloomberg
Trading: Energies
 
CobblersAwls's Avatar
 
Posts: 310 since Jul 2014
Thanks: 1,089 given, 386 received

Hello,

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.

https://www.coursera.org/course/rprog

Visit my futures io Trade Journal Started this thread Reply With Quote

Journal Challenge April 2021 results (now extended!):
Competing for $1800 in prizes from Jigsaw
looks_oneMaking a Living with the Microsby sstheo
(602 thanks from 60 posts)
looks_twoSalao's Journalby Salao
(147 thanks from 26 posts)
looks_3Learning to Profit - A journey in algorithms and optionsby Syntax
(112 thanks from 26 posts)
looks_4Deetee’s DAX Trading Journal (time based)by Deetee
(94 thanks from 30 posts)
looks_5Maybe a little bit different journalby Malykubo
(46 thanks from 29 posts)
 
Best Threads (Most Thanked)
in the last 7 days on futures io
I finally blew up an account
494 thanks
Spoo-nalysis ES e-mini futures S&P 500
63 thanks
The Crude Dude Oil Trading System
62 thanks
The tiyfTradePlanFactory indicator
21 thanks
Building a Crypto Mining Rig
18 thanks
 
(login for full post details)
  #3 (permalink)
 CobblersAwls 
London, United Kingdom
 
Experience: Intermediate
Platform: N/A
Broker: Bloomberg
Trading: Energies
 
CobblersAwls's Avatar
 
Posts: 310 since Jul 2014
Thanks: 1,089 given, 386 received


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.

Attached Files
Register to download File Type: xlsx CL Historical Data.xlsx (47.3 KB, 12 views)
Visit my futures io Trade Journal Started this thread Reply With Quote
 
(login for full post details)
  #4 (permalink)
devtycoon
Farmington, NM, USA
 
 
Posts: 7 since Aug 2016
Thanks: 1 given, 0 received


CobblersAwls View Post
The data I used was pulled from Bloomberg.

Have you looked in to quandle.com?

Reply With Quote


futures io Trading Community Trading Journals > Using Excel and learning VBA and R for Trading and Analysis


Last Updated on August 15, 2016


Upcoming Webinars and Events

NinjaTrader Indicator Challenge!

Ongoing

Journal Challenge w/$1,800 in prizes!

May 7

The Cold Hard Truth: Maybe I Am Not Good Enough w/Chris Gray @ Earn2Trade

Elite only
     



Copyright © 2021 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, Ph: +507 833-9432 (Panama and Intl), +1 888-312-3001 (USA and Canada), info@futures.io
All information is for educational use only and is not investment advice.
There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
no new posts