Excel Data Analyst Introduction - futures io
futures io futures trading



Excel Data Analyst Introduction


Discussion in Traders Hideout

Updated
      Top Posters
    1. looks_one semiopen with 10 posts (16 thanks)
    2. looks_two SMCJB with 3 posts (4 thanks)
    3. looks_3 xplorer with 2 posts (5 thanks)
    4. looks_4 Quick Summary with 1 posts (0 thanks)
    1. trending_up 2,056 views
    2. thumb_up 25 thanks given
    3. group 6 followers
    1. forum 14 posts
    2. attach_file 11 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
 

Excel Data Analyst Introduction

(login for full post details)
  #1 (permalink)
hillsborough nj
 
Experience: Advanced
Platform: Tradestation/Excel
Broker: TradeStation
Trading: emicro
 
Posts: 97 since Sep 2018
Thanks: 17 given, 44 received

This could have gone into the introductions but the word beginner was in there...

I've been working with Excel VBA for about 25 years, mostly doing data analysis of price history of major ETFs and stocks. Over the last year or so, I've been doing it full time.

I write articles for seekingalpha once in a while, my most recent one is https://seekingalpha.com/article/4276462-finite-state-accounting-improves-strategy-evaluation

I've been looking around for a suitable platform to discuss my work. Have a website; written a few articles. It could be turned into a software product, or maybe a book, Mostly interested in seeing the concept survive and gain recognition rather than making money from it.

Started this thread Reply With Quote
The following 7 users say Thank You to semiopen for this post:

Journal Challenge February 2021 results (so far):
Competing for $1500 in prizes from Topstep
looks_oneSBtrader82 's Trading Journalby SBtrader82
(170 thanks from 31 posts)
looks_twoJust BEING a Trader: Letting Go!!by iqgod
(120 thanks from 33 posts)
looks_3Wisdom is Emptinessby Mtype
(68 thanks from 25 posts)
looks_4Deetee’s DAX Trading Journal (time based)by Deetee
(31 thanks from 17 posts)
looks_5Journal for peanuts1956by peanuts1956
(23 thanks from 13 posts)
 
 
(login for full post details)
  #3 (permalink)
Site Moderator
London UK
 
Experience: Beginner
Platform: CQG
Broker: S5
Trading: Futures
 
xplorer's Avatar
 
Posts: 5,296 since Sep 2015
Thanks: 13,329 given, 12,599 received



semiopen View Post
This could have gone into the introductions but the word beginner was in there...

I've been working with Excel VBA for about 25 years, mostly doing data analysis of price history of major ETFs and stocks. Over the last year or so, I've been doing it full time.

I write articles for seekingalpha once in a while, my most recent one is https://seekingalpha.com/article/4276462-finite-state-accounting-improves-strategy-evaluation

I've been looking around for a suitable platform to discuss my work. Have a website; written a few articles. It could be turned into a software product, or maybe a book, Mostly interested in seeing the concept survive and gain recognition rather than making money from it.

Hi semiopen - You will find a few Excel aficionados here, I am an Excel junkie myself.

So you're more than welcome to discuss your work here and I'm sure many will be interested.


A word of caution though: this site has a very strict No self-promotion policy, which means anything that can be construed as self-promotion for monetary gain is a no-no.


Other than that, I look forward to read your work!

Reply With Quote
The following 3 users say Thank You to xplorer for this post:
 
(login for full post details)
  #4 (permalink)
hillsborough nj
 
Experience: Advanced
Platform: Tradestation/Excel
Broker: TradeStation
Trading: emicro
 
Posts: 97 since Sep 2018
Thanks: 17 given, 44 received

Thanks for the reply, Xplorer.

I'm a retired brokerage systems developer - front and back office. I decided to seriously continue development of my application, sort of a last project. Mostly I'm interested in feedback. The code/logic/methodology isn't a secret, mostly just a way of self expression similar to painting watercolors. I'm not sure how good my analysis is; seems pretty good to me - false modesty isn't one of my vices.

I think price action should be analyzed holistically - "characterized by comprehension of the parts of something as intimately interconnected and explicable only by reference to the whole." (from google)

This is a similar concept to Yin and Yang. "a concept of dualism in ancient Chinese philosophy, describing how seemingly opposite or contrary forces may actually be complementary, interconnected, and interdependent in the natural world, and how they may give rise to each other as they interrelate to one another."

Traders seem to be obsessed with situations that are profitable. I understand why profitability is important, but one has to wonder whether the single minded pursuit of an edge is the best way to achieve that.

Finite State analysis is a way to look at and hopefully understand the entire price action story.

I'll try to post concrete stuff now and again.

Started this thread Reply With Quote
The following 3 users say Thank You to semiopen for this post:
 
(login for full post details)
  #5 (permalink)
hillsborough nj
 
Experience: Advanced
Platform: Tradestation/Excel
Broker: TradeStation
Trading: emicro
 
Posts: 97 since Sep 2018
Thanks: 17 given, 44 received

I've been playing with graphics a little, meant to do a little writeup here but didn't understand what URL was needed to upload an image so I put it on my website - Three Types of Profit.

It has some analysis for SPY using the 31 day rate of change.

Started this thread Reply With Quote
 
(login for full post details)
  #6 (permalink)
Site Moderator
London UK
 
Experience: Beginner
Platform: CQG
Broker: S5
Trading: Futures
 
xplorer's Avatar
 
Posts: 5,296 since Sep 2015
Thanks: 13,329 given, 12,599 received


semiopen View Post
I've been playing with graphics a little, meant to do a little writeup here but didn't understand what URL was needed to upload an image so I put it on my website -

No need to link back to your website, you just click on the attachment button (highlighted in the pic on this post) and you upload your image



Once uploaded, you click on [Embed] before closing the upload window and you're done.

Reply With Quote
The following 2 users say Thank You to xplorer for this post:
 
(login for full post details)
  #7 (permalink)
hillsborough nj
 
Experience: Advanced
Platform: Tradestation/Excel
Broker: TradeStation
Trading: emicro
 
Posts: 97 since Sep 2018
Thanks: 17 given, 44 received



This is a pivot table, analyzing weekly LSMA (Least Square Moving Average) performance from April 3, 2006 through yesterday. The various columns are important to me and are described on my web site and seekingalpha (to some extent).

32 conditions are analyzed by the app - 8 different lengths of four indicators. The four indicators are: Simple MA, Exponential MA, Rate of change, and Least Square MA. I call the 8 lengths Friedmanacci numbers: 3,7,13,21,31,43,57, and 73. Possible timeframes are daily, weekly, and monthly.

There are two possible states for each condition - Above (A) and Below (B) zero (or some other number).

When the possible states are combined, as above, HProf is the buy and hold profit for the condition(s) being analyzed as well as the buy and hold profit for the instrument in general. The app invests a fixed amount of $10,000 every time the state changes and books the profit or loss for the previous state at the same time. Note that RProf (Reinvest) is higher than HProf for IWM for this time period.



This is the same information with the three profits types having running totals.

Note how IWM has lost money when above 13L but made money below.





The market turmoil during 2008 is partially to blame for the RProf higher than HProf anomaly.

There is a pile of observations that go with this analysis. For example, A is the "positive" state, "B" is the negative. A transition to a negative state makes money on the entry and exit, a positive state transition will lose on the entry and exit. Generally positive states last about twice as long as negative states. Negative states have a higher winning percentage but also have higher risk. LSMA has a remarkably even distribution of Above and Below lengths.

Entries and exits can be improved, the most obvious trading strategy is to buy the negative state at some point below the transition price and sell the positive state at some point above the transition price.

This being a futures forum, this may not apply as much, but some form of buy and hold is a great strategy for equities. I haven't heard many trading mentors dwell on that fact.

Started this thread Reply With Quote
The following 2 users say Thank You to semiopen for this post:
 
(login for full post details)
  #8 (permalink)
Legendary Market Wizard
Houston, TX
 
Experience: Advanced
Platform: Trading Technologies
Broker: Primary Advantage Futures. Also ED&F and Tradestation
Trading: Primarily Energy but also a little GE, GC, SI & Bitcoin
 
Posts: 3,968 since Dec 2013
Thanks: 3,259 given, 7,777 received

Greetings Josef and welcome to futures.io hopefully we can have some productive conversations.

semiopen View Post
I write articles for seekingalpha once in a while, my most recent one is https://seekingalpha.com/article/4276462-finite-state-accounting-improves-strategy-evaluation

Interesting article. While a simple 43 Week Moving Average Crossover system can quickly be programmed in something like Tradestation your AR0 and AR1 analysis would be very difficult to perform.

Given your AR0 and AR1 conclusions, if you now revisited the analysis trying to improve your states (switch earlier or later) do you feel that you would have introduced a bias?

I performed a similar analysis once and came to similar conclusions. It's well known that while the SPX has an upward bias it's short term movement is often mean reverting. Hence combining a shorter moving average and a longer moving average (NOT a crossover system) - ie creating a 4 state system - greatly improved results - until 2018 or 2019 when it imploded impressively!

Also given the 10 year bull run of the stock market it would have been interesting to see how it performed a) in 2008 and 2009 and b) commodity ETFs like GLD, UNG and USO.

I will read the rest of your thread in the coming days.

Reply With Quote
The following 3 users say Thank You to SMCJB for this post:
 
(login for full post details)
  #9 (permalink)
hillsborough nj
 
Experience: Advanced
Platform: Tradestation/Excel
Broker: TradeStation
Trading: emicro
 
Posts: 97 since Sep 2018
Thanks: 17 given, 44 received

I analyzed a 3 day 7 day rate of change strategy in late 2018, buying when both were positive, selling when at least one indicator turned negative. That worked well for 2017- mid 2019. I noticed it performed less well in 2015 (to say the least). That jump started this project as I decided to look more carefully at the performance of the two indicators as a four state system. Prior to that, I did all my strategy development on TradeStation. I had to program the strategy accounting on Excel and by the 10th iteration or so of the prototype, the algorithm got pretty good by my standards.

There is a technical argument that strategy development in its current form is unsound and I suspect that I have proven that although it is surprisingly difficult to interest people much less convince them. It is not so important if that is true or not, it mostly provides a design theology (if that makes any sense). My guess is that my stuff has some quite original views in some areas mixed with a certain amount of reinventing the wheel because I have no formal education in the areas of computer science, statistics, mathematics, or quantitative finance.

The charts above suggest a natural log relationship between the different profit measures (at least after a cursory glance - 2 weeks ago, I knew nothing about the subject), so I'm interested in doing something with that.

The accounting application can currently analyze 8 above/below conditions at a time. That can be increased simply by changing an array size.

Four states gives you something like AA occurring 50% of the time, BB about 25%, with AB and BA splitting the difference. At some point the population of the obscure states is too small to draw inferences and then there are naming convention issues.

Regarding non equity based futures, I think the analysis is still appropriate. When one analyzes the states in something non uptrending, certain states will produce losses, we don't really see that with the equity indexes - IWM is interesting that way. Just looking at some data briefly, the above state seems to become a loser before the below state.

Started this thread Reply With Quote
The following 3 users say Thank You to semiopen for this post:
 
(login for full post details)
  #10 (permalink)
hillsborough nj
 
Experience: Advanced
Platform: Tradestation/Excel
Broker: TradeStation
Trading: emicro
 
Posts: 97 since Sep 2018
Thanks: 17 given, 44 received


Here is some analysis based on state change entries. Trying to keep the Theocratic messaging to a minimum, but the typical strategy trader doesn't look at both sides as we are doing here. Whether my analysis is sound might be debatable, but my guess is that not considering stuff like this at all has to be a real disadvantage.

The table below is comprised of 16 major ETFs. DIA,IWM,QQQ,SPY,VTI,XBI,XHB,XLB,XLE,XLF,XLI,XLK,XLP,XLU,XLV, and XLY. Based on Daily data from 6/29/2012 through yesterday. The states of 32 indicators, 8 for each of the 4 types (E,L.M,R) are considered for each ETF. Accounting for virtual trades is done at all state transitions.



The groupings are by level of the indicator (x1) at a state transition. Observed results range from -10.8% to +17.4%. The averages are based on the fixed amount of $10,000 as the initial investment. The distribution of TLen (total length) CStat (count of state transitions) and CWin (count of wins) looks quasi normal to me. APPer is the average profit per period. Note how violent state changes to the upside are dubious to go long on.



The table above deals with 07M (7 day moving average). Note the distribution is still pretty normal. and the red/yellow flag over strong up moves.

Started this thread Reply With Quote
The following user says Thank You to semiopen for this post:
 
(login for full post details)
  #11 (permalink)
Legendary Market Wizard
Houston, TX
 
Experience: Advanced
Platform: Trading Technologies
Broker: Primary Advantage Futures. Also ED&F and Tradestation
Trading: Primarily Energy but also a little GE, GC, SI & Bitcoin
 
Posts: 3,968 since Dec 2013
Thanks: 3,259 given, 7,777 received


SMCJB View Post
I performed a similar analysis once and came to similar conclusions. It's well known that while the SPX has an upward bias it's short term movement is often mean reverting. Hence combining a shorter moving average and a longer moving average (NOT a crossover system) - ie creating a 4 state system - greatly improved results - until 2018 or 2019 when it imploded impressively!

This is an updated Tradestation Equity Curve for my Dual Moving Average System (again not a crossover!). Thankfully I never traded to it. And yes the all-time peak (Aug 2018) was when this was developed. This is one of big concerns with data mining to try and find biases. Very difficult to know when you have found something and when it's just spurious! This is why I asked "if you now revisited the analysis trying to improve your states (switch earlier or later) do you feel that you would have introduced a bias?"


Reply With Quote
 
(login for full post details)
  #12 (permalink)
hillsborough nj
 
Experience: Advanced
Platform: Tradestation/Excel
Broker: TradeStation
Trading: emicro
 
Posts: 97 since Sep 2018
Thanks: 17 given, 44 received

I'm trying to avoid biases. most of my thinking about possible trading strategies is how to integrate them into the VBA logic. Presumably some version of the state analysis I've been discussing would be used by the investment/trading routine.

It is pretty clear that buys should be done in the below state at some point and sells in the above. It seems obvious that the buy can wait to take advantage of the seemingly inevitable further deterioration of the indicator, but then there is the possibility of missing a move. I imagine virtually all one day trades in the below state are profitable, I meant to check that out today, the tables I posted here were originally an attempt to look at that but I got distracted by how cool they seemed to be.

Selling when an above state transitions to below appears to be wrong, certainly too late.

FWIW, I've been interested in EWMA - Exploring the Exponentially Weighted Moving Average

"The exponentially weighted moving average (EWMA) introduces lambda, which is called the smoothing parameter. Lambda must be less than one. Under that condition, instead of equal weights, each squared return is weighted by a multiplier..."

That looks better than my feeble attempts, I unknowingly reinvented a weighting method a few months ago that is used by Martin Pring in one of his K studies, maybe Mr. Pring has special insight but I concluded it was worthless.

Started this thread Reply With Quote
 
(login for full post details)
  #13 (permalink)
Legendary Market Wizard
Houston, TX
 
Experience: Advanced
Platform: Trading Technologies
Broker: Primary Advantage Futures. Also ED&F and Tradestation
Trading: Primarily Energy but also a little GE, GC, SI & Bitcoin
 
Posts: 3,968 since Dec 2013
Thanks: 3,259 given, 7,777 received

Hi @semiopen finally got around to reading your posts in detail.

While I found it very interesting I'm actually a little confused what the pivot tables show. For example

semiopen View Post
32 conditions are analyzed by the app - 8 different lengths of four indicators. The four indicators are: Simple MA, Exponential MA, Rate of change, and Least Square MA. I call the 8 lengths Friedmanacci numbers: 3,7,13,21,31,43,57, and 73. Possible timeframes are daily, weekly, and monthly.

There are two possible states for each condition - Above (A) and Below (B) zero (or some other number).

When the possible states are combined, as above, HProf is the buy and hold profit for the condition(s) being analyzed as well as the buy and hold profit for the instrument in general. The app invests a fixed amount of $10,000 every time the state changes and books the profit or loss for the previous state at the same time. Note that RProf (Reinvest) is higher than HProf for IWM for this time period.


So what does the pivot table show? I understand how the states work but is it one single indicator with one single time period (13?) or is it all 4 indicators with one single time period (13?) or am I completely misinterpreting "Condition 13L"

I recently did something similar in R but a lot larger, and only focused on next day returns not the total return until the state changes. I took about 30 conditions, some were price related, some volatility, some range, each having a True(1) or False(0) flag. I then combined the conditions in every single possible pair combination. So 30*2*29*2 = approximately 3500 different state combinations. For example One Pair would be Condition 1 False and Condition 19 True which might equate to Price Not Above SMA and Volatility High. I then ranked all possible pairs to see which performed best (potential buy) and worse (potential sale) and also applied some other filters (min number occurrences etc). I did this 4 months in a row, for 4 different symbols, picking pairs that historically did well. I then used the results to develop 4 systems that I then entered into a four 6 month 'incubation' competitions. Obviously I know this was some extreme data mining but was curious how they would perform. The first system is now 4.5 months in and after a few good weeks has turned negative. The second system is 3.5 months in and every single trade has been a loss. The third system is 1.5 months in and was up until a bad trade last week. The fourth system is 0.5 months in and has been stopped out on all 3 trades. So extremely ugly. Data mining at it's extreme worse.

With regards to EWMAs, you might want to also look at Adaptive Moving Averages. If you want to get really technical take a look at Digital Signal Processing. An EWMA is basically a Single Pole Low Pass Filter in DSP.

Reply With Quote
The following user says Thank You to SMCJB for this post:
 
(login for full post details)
  #14 (permalink)
hillsborough nj
 
Experience: Advanced
Platform: Tradestation/Excel
Broker: TradeStation
Trading: emicro
 
Posts: 97 since Sep 2018
Thanks: 17 given, 44 received

13L is a 13 day least square moving average. I'm changing the naming convention to L13. Both states are combined in the chart so it show the results of buy and hold in addition to the other two investment styles.

I was kind of thinking of drawing random lines as opposed to other indicators. Then the idea is to see how far back in time the line has to go to equalize returns between above and below that line.

This is a report i just created. It shows major ETFs and the current status of 6 rate of change strategies - R03 is the 3 period rate of change. R03P shows the number of days the current state has been active and R03R is the profit as a percent from the start of the state. If the state is above zero, R03P (or other P column) is positive; if it is below zero it will be negative.



This one is current state of exponential moving averages. Notice all XLV averages turned positive today. Unfortunately XLV doesn't play above the line real well.


Started this thread Reply With Quote
 
(login for full post details)
  #15 (permalink)
hillsborough nj
 
Experience: Advanced
Platform: Tradestation/Excel
Broker: TradeStation
Trading: emicro
 
Posts: 97 since Sep 2018
Thanks: 17 given, 44 received

I noted in the previous post that XLV doesn't do well in above states compared to below states.

The first two tables are for XLV performance above since March, 2015. Charts use running totals.






The next two are for below performance.




Started this thread Reply With Quote


futures io Trading Community Traders Hideout > Excel Data Analyst Introduction


Last Updated on September 12, 2019


Upcoming Webinars and Events
 

NinjaTrader Indicator Challenge!

Ongoing
 

Journal Challenge w/$1500 prizes from Topstep!

February
 

Battlestations! Show us your trading desk - $1,500 in prizes!

March
 

Call Option Buying: The New Pain Trade? w/Carley Garner

Elite only
     



Copyright © 2021 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