Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Channel in Excel

Posted on 2014-08-07
8
Medium Priority
?
1,256 Views
Last Modified: 2014-08-09
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
0
Comment
Question by:Naresh Patel
  • 4
  • 4
8 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40248027
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
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40248109
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40248552
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 8

Author Comment

by:Naresh Patel
ID: 40248786
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
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40248913
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
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40250374
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
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 40250376
Full Grad
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40251257
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question