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



Using Excel and learning VBA and R for Trading and Analysis


Discussion in Trading Journals

Updated by devtycoon
    1. trending_up 1,201 views
    2. thumb_up 0 thanks given
    3. group 1 followers
    1. forum 3 replies
    2. attach_file 3 attachments




Welcome to futures io: the largest futures trading community on the planet, with well over 100,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)
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:

https://www.youtube.com/watch?v=KHO5NIcZAc4


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 Reply With Quote

Can you help answer these questions
from other members on futures io?
Can anyone help convert this pine script to c# or vb?
MultiCharts
Sending an ACH from BoA to GFF Brokers
Brokers
Heat Map on Trendspider
Platforms and Indicators
MulitCharts: Limit Order converted to MIT
Platforms and Indicators
Anyone with Overcharts license/experience?
Platforms and Indicators
 
 
(login for full post details)
  #3 (permalink)
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 Reply With Quote
 
(login for full post details)
  #4 (permalink)
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


August 15, 2016


Upcoming Webinars and Events
 

Order Flow Tools w/MotiveWave & Zytrade Brokerage

Oct 22
 

Technical Analysis Workshop w/NinjaTrader

Oct 27
 

Bookmap

Oct TBD
 

SharkIndicators

Oct TBD
 

Carley Garner

Oct TBD
 

GruttePier

Oct TBD
     



Copyright © 2020 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, +507 833-9432, 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