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


Using Excel and learning VBA and R for Trading and Analysis
Started: by CobblersAwls Views / Replies:390 / 3
Last Reply: Attachments:3

Welcome to futures io.

Welcome, Guest!

This forum was established to help traders (especially futures traders) by openly sharing indicators, strategies, methods, trading journals and discussing the psychology of trading.

We are fundamentally different than most other trading forums:
  • We work extremely hard to keep things positive on our forums.
  • We do not tolerate rude behavior, trolling, or vendor advertising in posts.
  • We firmly believe in openness and encourage sharing. The holy grail is within you, it is not something tangible you can download.
  • 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, and we will never resell your private information.

-- Big Mike

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: 255 since Jul 2014
Thanks: 814 given, 314 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: 255 since Jul 2014
Thanks: 814 given, 314 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, 6 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)
 

Mastering Trading Pysychology w/Brett Steenbarger & Trading Technologies

Elite only

PTMC Protrader Platform First Look w/Sergey Sokolov

Elite only

Spring Grains Outlook w/Sean Lusk @ Walsh Trading

Elite only

FIO Video Journal Challenge featuring NinjaTrader ($2,000+ of prizes)

April

Process above all else w/Anthony Crudele @ Futures Radio Show

Elite only

Machine Learning - Quantitative Trading w/Martin Froehler @ Quantiacs

Elite only

Market Dynamics w/Peter Davies @ Jigsaw Trading

Elite only

Ask Me Anything w/Patrick Rooney @ Trading Technologies

Apr 18

Ask Me Anything w/FuturesTrader71

Apr 19

Machine Learning w/Kris Longmore

Elite only

Market Analysis w/Dave Forss

Apr 25

Introducing iSystems with Stage 5 Trading

Apr 27
     

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 08:17 AM.

no new posts
Page generated 2017-03-24 in 0.08 seconds with 20 queries on phoenix via your IP 54.204.252.37