Problem Using Solver with an optimization stock problem

Hey guys this is my first post/question. I am doing a project using data from a stock (netflix...or any stock for that matter). SKIP TO THE LAST TWO PARAGRAPHS, FOR A MORE SUCCINCT QUESTION WITH OUR ALL THE EXCESS INFORMATION)

With the data, i have calculated the %change in the price for each day for 3 years. I have an IF(AND) equation to find all the days in which there was a given 1% interval decrease in price (-4%to -3%, or -8% -9%, etc., and this is highlighted in red on my spreadsheet). My IF(AND) equation basically reads: if there is a given percent decrease in the price of the stock and the decrease is between (for an example) -3 and -4 percent, then what is the percent change of that stock the next day? I have Five of these IF(AND)statements with each one adding up one more day - This may sound very confusing, and I am struggling to explain it, but looking at the spreadsheet will make a lot of sense.

The project is to try and see if a stock decreases by a certain percentage interval, how often does that stock have a positive percent change within 1 to 5 days. Then off to the right hand side of my spreadsheet I count all the occurences that the stock price decreases by the particular percent interval, and I count of those occurrences, which ones had a positive percent change and then divide the two numbers. This gives me the "likelihood" or average of when the stock has rebounded, or become positive, within 1 day, 2 days, 3 days, 4 days, and 5 days. I also decided to see that if the stock rebounds, what is the average percent it does rebound - so in each column(1 day column, 2 day column, etc.) I used the conditional sum wizard to add all of the occurrences that had a positive percent change in stock price and divided that number by the number of occurences that the stock saw a positive percent change.

NAIN QUESTION: In the past if ABC stock decreased within 4 to 5 percent on a given day, how often did the stock have a positive percent change within one day, or within two days, (and up to five days) And according to how often the stock does become positive with the five day range, what is average amount that the stock has increased?

Ultimately, I want to use solver to show me the optimum percentage interval that maximizes the average the stock increased across the five day time period. The attachment has explanations, and is easier to understand than my poor wording. Thanks!

Ultimately, I want to use solver to show me the optimum percentage interval that maximizes the average the stock increased across the five day time period.

You are using “COUNTIF” but solver can’t cope with discontinuous functions i.e. “IF”, “COUNTIF” and “SUMIF” so you will to change the formula in range N5:R6.

An alternative could be a macro that loops changing the value in N2 from say -1% to -9% in step of
-0.1% and writes the result to the sheet so you can see where maximum value is.

Alf

Thanks for the advice, as well as the info. about solver not working wih discontinout functions. I will try to come up with a different method of getting the same data for the range N5:R6. With the Macro, when I start recording it, is there a quick method of inputing the -0.1% steps, or would I just need to do that manually? Thanks again.

is there a quick method of inputing the -0.1% steps

Yes but numbers mus be adjusted a bit i.e. stepping from -1% to -9% did not work but stepping from 10 to 90 in steps of 1 works so deviding the result by 1000 and setting the value to be negative works. You need to change A3 to your target cell and add a macro that copies the result to another part of the spreadsheet so you can examine the results.

Please Login or Register  to view this content.

Alf

Have added a macro that loops from -1% in steps of -0.01% to -9% and there are more "problems" that makes the present setup unsuitable for Solver. To test run macro "DataLoop"

Look at the result I see that the values oscillate a thing that Solver don't cope with. For example you set Solver to maximize a value and Solver handles this by increasing the value of the adjustable cells and checks if target value increases. If so Solver does another increase of adjustable cells value and checks target value it it's bigger than previous value.

Solver keep on doing this until an increase of adjustable cells makes target value less than previous value so Solver then goes back one step present this target value as the maximum value. Solver can handle one maximum or one minimum if there are more maximums or minimums then solver will find a value based on the starting value of the adjustable cells.

Looking at the values in range T2:U82 one could start at an N2 value of -3.6% this gives a 5+ value of 36.36% changing N2 to -3.7% and 5+ goes up to 41.18% and -3.8% in N2 changes the 5+ value to 42.42%. When N2 is -3.9% 5+ value drops to 37.93% so obviously there is a maximum around 3.8% for N2. But there also seems to be a maximum at -4.8% where 5+ reach 50% and another maximum at -5.4% and -6.2%.

Alf