Does anyone know how to mathematically determine a security's trend? I can visually look at a chart of the Dow 30 and see that it has been trending up over the last 4 days. But, how do determine that using an Excel formula. Especially when you consider that you can have a down day and still be in an up trend.
Are you asking for a common, widely accepted, industry standard, exact definition of what a trend is? Or do you have your own definition and just want to know what it would look like as an excel formula? Or do you just want an excel spreadsheet that will tell you whether a stock will go up or down? voodoo
close(t) - close(t-4) is just what you need. the major trend lasting 4 days. may want to divide close(t) by high(t-4...t) to see the current drawback. that is just what a trend is built on.
What I am trying to do is get Excel to mathematically see a chart. I can visually look at a chart and see certain patterns and trends. I would love to have a spreadsheet that tells me whether a stock will go up or down -- especially if it is an accurate spreadsheet. However, for now I am concentrating on what the stock did in the past. You know, I have thought about just taking today's closing price versus a couple of days ago to see if it is higher or lower. But, is that a truly accurate gauge of trend? For example, you can have a stock that closes up a dime every day for the last 10 days. But, today it closes down 50 cents. The above formula shows today to be a down trend. This could be a start of a new downtrend. Or, if tomorrow is an up day, it could be just a trading anomaly and the upward trend continues. I am thinking that the answer is a comparison of today's close to a short-term SMA/EMA. Using a 20-day EMA shows the longer overall trend. But, it tends to be too effective at smoothing out short-term (3-5 days) trend reversals.
Trayder, i am a technical analysis programmer. the "formula" i named describes exactly what forms a "Trend". A trend is not broken until a counter movement greater than your defined minimum trend length occurs. It is a question of your definition what a minimum trend is. P.S.: forget about MA, EMA, SWMA and the other stuff. believe me, i know what i'm talkin about. maybe they did well 15years ago, but for todays markets not realistic.
You could try the ADX/DMI formulas to determine whether you're really in a trend for your period and how strong the direction. Alternatively you could try a linear regression over your target period and use coefficient of correlation and standard deviation boundary containment to determine how well it's really trending. A simple close(0)-close(-n) also gives some directional information but doesn't necessarily specify a "trend".
I think ArchAngel has given you some good advice. For questions such as yours it's good to consult trade publications like "Technical Analysis of Stocks and Commidities". In fact the Jan. 2002 issue (the one currently on sale) there's a short article: "Detecting Trend Direction and Strength" which is probably of interest to you. The author uses ADX and MACD. Let us know what you think of the article (good or bad). I hope this helps. Richard
Forgot to mention, if you're not math adverse you might want to also consult the book by John Ehlers, "Rocket Science for Traders". In particular chapter 10, "The Instantaneous Trendline" and chapter 11 "Identifying Market Modes" may be of use to you. Richard