Channel in Excel

Hi Experts,

Do any one have a idea to find recent channel of stock price and its channel extended values in excel numerically i.e. not in chart. it is just a thought so ideas suggestions -  please - greatly appreciated.This is What i required in Numerical (In Excel)
Attached is Numerical values of what is represent in candle chart.

Thank you
Channel.xlsx
LVL 8
Naresh PatelTraderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
I read several articles on channels and none provided a mathematical model for creating a channel like you show here.  Most actually state something along the lines of "choose a high point, draw a line that trends along other high points" then do the same for low points.  It's quite arbitrary.

One site (stockcharts.com) uses a 20-day high and low as a moving value alongside the candlestick plots, but it is not smooth and linear as your graph - and most channel/trending graphs -shows.

-Glenn

See also:
http://www.investopedia.com/articles/trading/05/020905.asp
0
Naresh PatelTraderAuthor Commented:
Mr.Glenn,

1st of all thank you for attempting this as I guess it is totally out of your expertise  i.e. Coding and all,  This is Stock Market Concept.

What if I have ZigZag points (see attached ) & formula or Code look back from recent point to 3 month back points & plot appropriate channel i.e. taken all points inside the channel with margin of 2 - 3 points. if points significantly out of the channel then there is no channel.

It is just a thought - correct me if I am wrong.

Thanks
Channel.xlsx
0
Glenn RayExcel VBA DeveloperCommented:
Well, I'm not totally unfamiliar with technical trading, but I will admit that my opinion of it is along the lines of soothsaying and mumbo jumbo.  No amount of technical analysis can replace good market research on a stock.

That said, you could also use a linear regression method with a variable sigma that encompasses a period's high and low values.  It would approximate the channel but only for shorter periods where the R value for the linear trend is high (say, > 0.5).

Regards,
Glenn
Sent from my Windows Phone
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Naresh PatelTraderAuthor Commented:
May i have sample or say demo file of what you stated above...pls

And apology for future past and present if my words are not properly frame. As English is not my primary language.
Thank you
0
Glenn RayExcel VBA DeveloperCommented:
I've added a linear trend analysis for the stock from 3/3/2014 to 8/7/2014.  There's one formula in column G
=TREND($F$2:$F$109,$A$2:$A$109,A2,TRUE)
that calculates the projected linear trend and then two additional formulas in H & I that plot the upper and lower bounds based on an arbitrary Q-Value (aka "sigma") of 15 that closely encompasses the highs and lows for this period.  Of course, there could be two different values to determine the upper and lower bounds, but decided to leave this simply with one value.

I plotted out the data also, but just the close points rather than a candlestick.   You could create these two lines with a candlestick chart also; you'd just change the chart type to Line for both.

Regards,
-Glenn

Edit:  I added the R-squared value for the linear trend to the workbook.  For this set of data the value is 0.93, which is a very good fit.
EE-Channel.xlsx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naresh PatelTraderAuthor Commented:
Pore perfect very very useful     - awesome  - mind-blowing - as far as this question, this  is solved  but just in case if I need your assistance may ask you? those formula is totally new for me.


Thanks Mr.Glenn
0
Naresh PatelTraderAuthor Commented:
Full Grad
0
Glenn RayExcel VBA DeveloperCommented:
I'll be glad to try and help.  Statistical functions are not my specialty because I don't get to use them very often.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Math / Science

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.