Beginners question - Help to create prediction model

Im very very very new to this. I have also posted this in an excel forum but I thought that maybe someone here can help me.
I want to build a prediction model if we should buy stocks or not. And by using an external parameter (demand from customer on the whole market in our area which we get daily) I want to predict if the stock price tomorrow goes up or down.

I have columns with:
Daily prices for last 10 months.
The price change per day in %
The price change per day in $
Customer Ratio (The variable Im suppose use to predict with)

I want excel by the end of each day to look at the daily %change and on the customer ratio and predict tomorros price, and give me a signal if I should "Buy / No buy" for TOMORROW (not the same day since it just closed). We always sell after 24 hours if excel doesnt tell us otherwise.

a) Maximize the profit
b) Keep the number of "loss-days" to a minimum (I.e control the risk!)
b) What threshold should be used on the customer ratio? I.e when is it good to buy or not?
c) What should the %-change be?

For example...I want to be able to say:
Okey if the customer ratio is below 4 and the price went up 5$ yesterday, we should buy today.


See attached excelfile. But I think I have done the whole solution in the wrong way. Maybe SOLVER Or Regression analysis is the way to go, but Im not sure.

The excelfile is available on my google drive:


Active Member
Excel is not suitable for this. The described time series analysis, with a hope of making money, should be programmed in a serious statistical software package. The best packages for time series are R, Matlab, Stata and EViews, in the descending order of greatness (in my opinion). SAS also has rich time series functionality but it might present a steep learning curve for a novice.

You can do many things in R, for example, but in Excel your capabilities are limited. You are unlikely to make money. Even stubborn, old school traders at places like Morgan Stanley are moving away from Excel.
Just two points to add to staassis's answer:

- Agreed that Excel is not a good tool for this analysis. To the list of tools already provided I would add Pandas which is a Python library. It sounds a little bit intimidating built for these types of time-series analysis (originally an in-house tool spun out of a hedge fund).
- There are limited profits in trading on systems built from end-of-day data. You should look at high-frequency intraday data (1-minute ohlcv bars or tick data). There are some test datasets here to built models on. Also, you should build in a 0.1% - 0.25% charge on each trade to simulate the bid/offer spread or the risk of slippage (ie missing the price you intended to trade at).