How to calculate multiple sets of standard deviation to find the best set in price data

Originally Posted by Fractalfield

I am trying to figure out how to go about taking a history of price changes of a market [...] measuring extremes in the range of price in the cycle from each cycle beginning, and then calculating for a deviation. [....] How can I go about setting up and configuring definable metrics of the cycle to read the data against a standard deviation formula and outputting in the spreadsheet (if possible) the values calculated?

First, let me say that the problem I think you describe is very interesting. Unfortunately, it might also be a little too broadly scoped for this forum, which usually answers specific questions about Excel per se, not problems that we might use Excel as a tool to solve.

Second, I hasten to point out the need to be precise in your terminology. For example, "deviation" is not the same as "standard deviation". It is unclear which would be more helpful for your purposes.

For another example, you say your "cycle length" is 100 days. Then you describe much shorter "cycles": a "cycle" when the price is going up, followed by a "cycle" when the price is going down. And you say that continues until you have "100 cycles". It is difficult to understand how you intend to define "cycle".

Finally, the verbal description is a little much to swallow. It might be helpful to have a "picture" of what you are trying to describe. For example, take a small sample of actual S&P 500 "prices" for an appropriate period, and demonstrate how you might manipulate the data to get the desired result.

I realize that is part of the problem that you want help with, namely how to use Excel for that purpose. But you must have a modicum of rudimentary Excel skills in order to proceed; at least the ability to create formulas that add, subtract, multiple, etc. Simply fill in the values that you would like to calculate by algorithms eventually. A mock-up.

Are you familiar with Bollinger bands? Is that what you have in mind; and are you seeking ways to automate that?

BTW....

Originally Posted by Fractalfield

from the beginning of the cycle, lets say on the first cycle the price is going up and makes a high price on the 10th day. Then on the next cycle the price is going down and makes a low on the 11th day, and on the third cycle the price is going down and makes a low price on the 9th day

You might want to reset your expectations. Looking at the S&P 500 from Jan'09 through Jan'16, the number of consecutive "price" increases or decreases has been only 1 for about 52% of the time, 2 for 23%, 3 for 13%, 4 for 7%, 5 for 3% and 6 for 2%. I suspect you want to look at periods of up and down trends, not consecutive trade days.

But honestly, I've struggled myself with finding an algorithm that distinguishes the two the way that our eyes do when looking at a chart. Obviously, look at changes in the direction of slope; but for how long?

(A simple moving average still does not do the trick, IMHO.)

Good luck!

My apologies for not being very specific. I envision an interface with a spreadsheet of price data. That allows me to input two values: one of which is how long a cycle should be, and the other is how many periods of time I want to measure for from the beginning of the cycle.

If there are 1000 days of data I have, and my cycle length is 100 days. Then I will have 10 cycles of data to measure. What I am measuring are the amount of extreme highs and lows of price, specifically what days they happened on, per cycle.

Manual experience has shown that extreme highs and lows in prices will fall within certain ranges of time when measured from the beginning of the cycle. Now I want to measure cycles in a specific manner across many markets. But manually would take years. So what I need to do is create an excel macro that works with the price history of the dates whether days or hours whatever and can take measurements of a range of different cycle configurations say 100 days with 8 price swings, or 50 days with 12 price swings, to then analyze those and choose which combination produces the smallest standard deviation of time. The method has shown that bad combinations of cycle lengths with the wrong amount of points produce wide ranges of stddev, while the right combination produces small range of stddev.

Example. In 10 cycles of 100 days per cycle, the minimum days elapsed for price to make a high or a low after the beginning of the cycle was 3 days. The maximum ws 10 days. The median is around 6.5. But I have 10 cycles so I want to see where the bulk of the swings happened because to use the min to max range is far too wide, and to use the median is too exclusive. Also the mean is too exclusive. Stddev of the 10 measurements I have gives me a best guess of the time range within reason.

Hope this makes sense. So what I need excel to do is when I press then button ideally it would analyse a range of combinations of cycle length versus number of swings in the cycle say... starting at 10days with 3 swings, going up to 10days with 10 swings. Then output which combination produces the smallest deviation. For sake of argument lets say that in the combination with 10days and 3 swings, the stddev of each price swing is 2days. So the minimum stddev is -2day to the average and +2days to the average. The total length of the range of time of the stddev would be 5days. But in the case of the combination of 10days with 7 swings, the stddev is 1day. So the total stddev range of time would be 3days.

I am aware that some of my points could be wrong mathematically, I am still new to this way of understanding. I am still learning. However it shouldn't detract too far from the point of question being given a set cycle length in a time series of price data how to measure the standard deviation of price extremes in time from the total amount of cycles measured.

Hope this helps to peel the veil back some. Thanks for your reply!