NexusFi: Find Your Edge


Home Menu

 





Using Excel and learning VBA and R for Trading and Analysis


Discussion in Trading Journals

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




 
Search this Thread

Using Excel and learning VBA and R for Trading and Analysis

  #1 (permalink)
 
CobblersAwls's Avatar
 CobblersAwls 
London, United Kingdom
 
Experience: Intermediate
Platform: N/A
Broker: Bloomberg
Trading: Energies
Posts: 310 since Jul 2014
Thanks Given: 1,089
Thanks Received: 386

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 NexusFi Trade Journal Started this thread Reply With Quote

Can you help answer these questions
from other members on NexusFi?
My NT8 Volume Profile Split by Asian/Euro/Open
NinjaTrader
NexusFi Journal Challenge - April 2024
Feedback and Announcements
Request for MACD with option to use different MAs for fa …
NinjaTrader
ZombieSqueeze
Platforms and Indicators
 
Best Threads (Most Thanked)
in the last 7 days on NexusFi
Retail Trading As An Industry
67 thanks
Battlestations: Show us your trading desks!
48 thanks
NexusFi site changelog and issues/problem reporting
47 thanks
GFIs1 1 DAX trade per day journal
32 thanks
What percentage per day is possible? [Poll]
31 thanks

  #3 (permalink)
 
CobblersAwls's Avatar
 CobblersAwls 
London, United Kingdom
 
Experience: Intermediate
Platform: N/A
Broker: Bloomberg
Trading: Energies
Posts: 310 since Jul 2014
Thanks Given: 1,089
Thanks Received: 386


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
Elite Membership required to download: CL Historical Data.xlsx
Visit my NexusFi Trade Journal Started this thread Reply With Quote
  #4 (permalink)
 devtycoon 
Denver, CO
 
Experience: None
Platform: none
Trading: SPY 0DTE
Posts: 18 since Aug 2016
Thanks Given: 9
Thanks Received: 16


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

Have you looked in to quandle.com?

Reply With Quote





Last Updated on August 15, 2016


© 2024 NexusFi™, s.a., All Rights Reserved.
Av Ricardo J. Alfaro, Century Tower, Panama City, Panama, Ph: +507 833-9432 (Panama and Intl), +1 888-312-3001 (USA and Canada)
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.
About Us - Contact Us - Site Rules, Acceptable Use, and Terms and Conditions - Privacy Policy - Downloads - Top
no new posts