Using Excel and learning VBA and R for Trading and Analysis - Trading Journals | futures io social day trading
futures io futures trading


Using Excel and learning VBA and R for Trading and Analysis
Updated: Views / Replies:565 / 3
Created: by CobblersAwls Attachments:3

Welcome to futures io.

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

futures io is the largest futures trading community on the planet, with over 90,000 members. At futures io, our goal has always been and always will be to create a friendly, positive, forward-thinking community where members can openly share and discuss everything the world of trading has to offer. The community is one of the friendliest you will find on any subject, with members going out of their way to help others. Some of the primary differences between futures io and other trading sites revolve around the standards of our community. Those standards include a code of conduct for our members, as well as extremely high standards that govern which partners we do business with, and which products or services we recommend to our members.

At futures io, our focus is on quality education. No hype, gimmicks, or secret sauce. The truth is: trading is hard. To succeed, you need to surround yourself with the right support system, educational content, and trading mentors Ė all of which you can find on futures io, utilizing our social trading environment.

With futures io, you can find honest trading reviews on brokers, trading rooms, indicator packages, trading strategies, and much more. Our trading review process is highly moderated to ensure that only genuine users are allowed, so you donít need to worry about fake reviews.

We are fundamentally different than most other trading sites:
  • We are here to help. Just let us know what you need.
  • We work extremely hard to keep things positive in our community.
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts.
  • We firmly believe in and encourage sharing. The holy grail is within you, we can help you find it.
  • We expect our members to participate and become a part of the community. Help yourself by helping others.

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

Reply
 
Thread Tools Search this Thread
 

Using Excel and learning VBA and R for Trading and Analysis

  #1 (permalink)
Elite Member
London, United Kingdom
 
Futures Experience: Intermediate
Platform: N/A
Broker/Data: Bloomberg
Favorite Futures: Energies
 
CobblersAwls's Avatar
 
Posts: 281 since Jul 2014
Thanks: 961 given, 357 received

Using Excel and learning VBA and R for Trading and Analysis

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

Reply With Quote
 
  #2 (permalink)
Quick Summary
Quick Summary Post

VBA Beginner Tutorials - https://www.youtube.com/watch?v=KHO5NIcZAc4

R Programming Course - https://www.coursera.org/course/rprog

 
  #3 (permalink)
Elite Member
London, United Kingdom
 
Futures Experience: Intermediate
Platform: N/A
Broker/Data: Bloomberg
Favorite Futures: Energies
 
CobblersAwls's Avatar
 
Posts: 281 since Jul 2014
Thanks: 961 given, 357 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.

Please register on futures.io to view futures trading content such as post attachment(s), image(s), and screenshot(s).



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:

Please register on futures.io to view futures trading content such as post attachment(s), image(s), and screenshot(s).



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, 9 views)
Reply With Quote
 
  #4 (permalink)
Trading Apprentice
Farmington, NM, USA
 
Futures Experience: Beginner
Platform: DAS
Favorite Futures: Equities
 
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

Reply



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

Thread Tools Search this Thread
Search this Thread:

Advanced Search



Upcoming Webinars and Events (4:30PM ET unless noted)
 

RandBots: AutoTrading Bots to diversify your trading

Oct 24
 

TopstepTrader: Profiting Without Risking Your Own Money

Oct 26

Building Open Source Indicators for NT8 w/Fat Tails @ Lizard Indicators

Elite only
     

Similar Threads
Thread Thread Starter Forum Replies Last Post
Ichimoku signals in Excel VBA lpsarathi Emini Index Futures Trading 5 July 23rd, 2016 09:50 AM
Quantitative Research Question: Which platform to use instead of EXCEL with VBA? Cogito ergo sum Platforms and Indicators 3 December 5th, 2013 05:30 PM
Learning how to play guitar...reminds me of learning how to trade shodson Beginners and Introductions 10 September 16th, 2013 05:22 PM
[Other]       Excel Analysis Tool? DarkPool Platforms and Indicators 12 November 15th, 2011 07:41 PM
Trading Learning Understanding tony2604 NinjaTrader 17 September 28th, 2010 01:59 AM


All times are GMT -4. The time now is 06:56 AM.

Copyright © 2017 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
Page generated 2017-10-20 in 0.08 seconds with 20 queries on phoenix via your IP 54.156.67.122