Beginners question - Help to create prediction model

#1
Hi!
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.

I CANT FIGURE IT OUT!

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: https://drive.google.com/file/d/16wOT0GVTxh74gdg-IqiO61nfQxCOymBa/view?usp=sharing
 

staassis

Active Member
#2
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.