Hello Traders

does any one know a platform or excel add in that can calculate things like below and with a distribution of results:

1. If XX goes up 5% in 2 days what is the outcome for day by day for next 10 days. And then show the result as a distribution. As example, for the last 100 times this happened stock went up more than 1% day 1 after in 65 of the outcomes

2. If stock hits new ATH then what happens next day, next 10 days, next 100 days. And then again with clear check, in the last 20 cases 15 went up and 5 down etc

Is there any good platform or software to do this sort of analysis?

Kind regards

Loop

That would be very easy to do in Morningstar's Commodity Excel Addin ( Link) or Commodity Query ( Link) but unfortunately it's not cheap software.

Query would look something like

SHOW
0: Close of SYMBOL
1: Close of SYMBOL 1 value later
2: Close of SYMBOL 2 value later
3: Close of SYMBOL 3 value later
4: Close of SYMBOL 4 value later
When
(Close of SYMBOL / Close of SYMBOL 2 values ago) > 1.05

You'd have to set up your own histogram in Excel though.

Thinking about it, it wouldn't be that difficult to do in easy language (and assume other backtesting software) but would involve a little more work.

 

Code

Initialize a Counter with Value 11 For each Bar - Increase Counter by 1 - Calculate if Close/Close[2]>1.05 & if so set counter to 0 - If Counter is < 11 then print to file a) Date b) Counter value c) Close d) Close/Close[2] etc

Then analyze in Excel.

I'm not an expert but I know my developer uses Mathematica from Wolfram Research a lot. Possibly just as he owns it.

The online Alpha system does neat stuff

Wolfram|Alpha Examples: Money & Finance

It would be quite easy to do this with Python and Pandas.
Just google Anaconda python.