Stock Data Analysis

[Shaun]

Hi All


Is it possible for excel to look at the current closing prices and volumes and then look at history of stock closing prices and volumes to find a period of time where the a similar trading had occured then provide an idication of what might happen whether it be adding another line in a graph, providing a buy/sell signal or a combination of the two.


Cheers


Shaun

 

[SirJB7]

Hi, Shaun!


If Excel finds a suitable source (workbook, text file or any other that can be imported, website with a structure that allows getting data), if you define how the search must be performed and how the similar trades can be identified, if there's a clear and neat rule that can be followed by Excel to provide what might happen, among other considerations, the answer would probably be yes, maybe it's possible.


So please elaborate a bit more on your idea, upload a sample workbook with your work on the suject until now and tell us where you're stuck, define the sources actual and historic and how is the information stored (links, files, samples?), explain us what are the business rules you want Excel to control or report, and we'll see what we can do for you.


I'd recommend you to read the three green sticky posts at this forums main page for general guidelines. It'll give you a lot of information about how you can get the best results here.


Regards!

 

[Shaun]

Hi SirJB7


Your comments are have been taken on board and green stickies have been read.


This is more a concept discussion than a please help discussion (though help would be greatly appreciated).


The state of play as it stands now:

I have (with the help of a good friend) developed a workbook which establishes a bunch of stock workbooks(one workbook per share), updates the data either on a daily basis or over multiple days until the data is up to date. From there I can select (via a list) a stock and a time period (1 week, 1 month, 3 months...5 year) that I wish to view and the VBA opens the data workbook, performs the calculations for the technical analysis for the entire data period and the plots the graphs.


At this stage, I need to view each stock's graph and make a decision based on what I see. I would like to effectively remove myself from the equation and at the click of a button have Excel perform the same calculations that it already does, analysis the raw close and volume data to find same/similar condition/trends occurring in the past as is currently occurring in the stock today and make a prediction about what is likely to happen based on the past data. I would like the analysis to take into account multiple instances of same/similar conditions and report that back. Then give each of the indicators a score out of 10, arbitrarily, and then generate a list of stock to buy and stocks to sell.


So I guess I am after some input into where do I start, there is so much in what I am trying to do, I guess I just don't know what the first piece is to tackle, what tools I will need to tackle it, then to implement it.


Just thinking now, I would need to understand what it is I want to find. Which would be analysing the date, close and volume data (which is the basis for just about all technical analysis). The next step would be how to measure the conditions/trends. Here I am thinking percentage increases and decreases, as all stocks trade at different amounts, and varying amounts with the same stock percentage movements would bring everything back to something comparable across all stock. Is there a better method, statistics is not my friend, but we are getting there.


Cheers


Shaun

 

[SirJB7]

Hi, Shaun!


I must thank you for pristine exposition of the project status.


I'm happy to know -and were my highly alert signals- that:

a) You have the source. Well or bad structured, is just a matter of formatting, but the main issue here would have been getting the data from the web, financial services or whatsoever origin it might be.

b) You use that source from within a series of workbooks, either stored in one place or another, accessing it by formulas or functions.

c) You have a data extract/report with the needed presentation for analyzing and deciding, either in numerical or graphic way.

d) You have the business rules to apply to this data presentation in order to make a decision.

e) You have an idea of the methodology that could be used to automatize this decision process.


With a), b) & c) you have the whole set of technical (in this case Excel based) tools to be used in conjunction with d) (i.e, your know-how) to achieve the goal.


You just need to build an ordered (let's say logic, standardized an homogeneous) procedure or what you do now manually, starting from d). How going on? Passing thru e), which will be the core of your project as it will replace you and your business skills and should provide a better or equal solution as of today's yours one.


Being re-started as a concept discussion rather than a help discussion, well, let's discuss over the concepts. As a matter of fact you have yet completed many phases of the whole process, and for the remaining phases the basic theory has been exposed.


What do you have that I don't (or we as many other might be reading and following this topic even they don't write in it)? Nothing less than this:

- the workbook with the source data structure (don't mean necessarily the actual values, but yes a full set ranging all over conditions to be tested -remember the reference to dummy data in the green posts-)

- the graphs which you analyze and with which you made decisions

- the so called business rules or to-do-list properly ordered & KPI set (key process indicators and criteria for automation)


Whether you wish to go on elaborating in an abstract way or advance to the design model, there it resides the clue to the information you should share.


Go on with theory? Feel free to perform any action (define, ask, comment) and I'll try to follow you without getting lost among financial issues (please note that I'm not a NYSE specialist).


Ready to make your next move? Well, firstly let me get my hands on the data structure, then explain how do you do things manually today, how do you think that the scoreboard should look like, and at last let's discuss any implementation idea that might have arisen or emerged from this process.


Regards!

 

[Shaun]

Hi SirJB7


Firstly, thank you taking the time to read my posts.


Secondly, I guess it is time to see how deep the rabbit hole goes! More quickly than I had anticipated, but I am more than happy to jump right on in!


This is a project that started in November(ish) last year, and at that time I was excited by the sumif function. So my excel skills are relatively (relative to Excel's capabilities) poor at best. I have since learned a bit about VBA, enough to be able to complete simple tasks.


a) You have the source. Well or bad structured, is just a matter of formatting, but the main issue here would have been getting the data from the web, financial services or whatsoever origin it might be.


This part I am fairly happy with, I have VBA code which generates two different URL addresses. The first sets up the individual stock workbooks and obtains the historical data from Yahoo finance and performs the various technical indicator calculations (this may change as the file size for each stock is bloated). The workbooks to be created are generated from a stock list which can be one stock or all stocks.


The second bit of code is essentially stripped out version of the first which just inserts a new row and updates the daily data and performs the technical indicator calculations.


Resulting raw data file: https://rapidshare.com/files/161901302/aac.ax.xlsx (I hope this works)


b) You use that source from within a series of workbooks, either stored in one place or another, accessing it by formulas or functions.

c) You have a data extract/report with the needed presentation for analyzing and deciding, either in numerical or graphic way.


I think it would be best to tackle these together.


Again using VBA code the three graphs are generated resulting in:


https://rapidshare.com/files/842319905/Graphs.pdf (Again, I hope this works)


missing from that pdf are three buttons:

1. Create Workbooks

2. Daily Update

3. Create Charts


Okay this post has been huge but I think it covers parts of your last paragraph in a fair degree of detail.


I will complete the "explain how do you do things manually today" shortly. That will again be a fairly lengthy post.


Cheers


Shaun

 

[SirJB7]

Hi, Shaun!


Got the files so uploading method worked. Opened them. Waiting for next chapter.


If you liked SUMIF function, give a look to SUMIFS and after that to SUMPRODUCT. Here at chandoo's site you can start right by:

http://chandoo.org/wp/2008/11/12/using-countif-sumif-excel-help/

http://chandoo.org/wp/2010/04/20/introduction-to-excel-sumifs-formula/

http://chandoo.org/wp/2009/07/22/sumif-with-multiple-conditions/

http://chandoo.org/wp/tag/sumifs/


BTW, if you feel more comfortable with Word instead of writing everything here just make a zip (or rar or compressed file) and include doc there, and then briefly explain something in your comment and add the download link.


Regards!

 

[Shaun]

Hi SirJB7


I will certainly have look through those links.


The first of the three graphs are a simple Open, High, Low, Close graph, with two exponential moving averages (EMA) laid over the top. As you could see from the data workbook aac.ax.xlsx all the indicators a based on historical information and it is those trends plotted that provide insight as to what might happen tomorrow.


When the shorter EMA is below the longer EMA the stock is going down, the opposite is also true. The gap between those lines is an indicator of the strength of that movement. This provides what is known as the Moving Average Convergence-Divergence (MACD – graph 2). This is essentially a measure of momentum. The EMA in the graph is of the MACD, providing a “signal” line. The Relative Strength Index (RSI) attempts to measure the speed and change of price movements. RSI considers a stock to be overbought when it exceeds 70% and oversold when below 30%.


The third graph is a stochastic oscillator, signal line, and the volume traded. It attempts to indicate price turning points. When the signal lines cross 80% or 20% it is indicative of a shift in the price direction.


When looking at these various indicators it is possible to make assumptions about what may happen.


What I am proposing is ask Excel to somehow take the price and volume, look at the history available, find a similar set of circumstances, look at what the actual outcome was and plot an extension to the existing graphs as to the likely outcome based on what Excel “saw”. As all the indicators described above are essentially based on the close price, I think it is the close price that should be the focus of the analysis. However volume also plays a key role. The real influence of any given stock price is likes of managed investment funds and the like (I will call them big money). They have billions of dollars with which to buy and sell stocks, which has hundreds of researchers behind each trade. It is this group that is most likely to have access to information, such as an announcement that a company has an increased revision of their profit for the year, or the opposite, a decreased revision of their profit. The only way to get any indication as to what big money is doing is through volume, so I would like to incorporate the movements of volume in to the analysis. If volumes are also similar, I think the calculation will become more reliable.


The Goal:


For each workbook contained in a stock directory, get excel to look at the current period, which of course needs to be variable (say 5 days to 60 days in increments and possibly longer); look at the history for similar circumstances (with some degree of freedom to how similar – 5%, 10%); look at what happened until the next major price movement occurred; apply the results to current data and calculate possible outcome for the selected period.


Excel based on the outcome would then update/create a list to display either a buy (price is low and about to go high), sell (price is high and about to go low) or hold (price is expected to remain flat).


When looking at the graphs, the outcomes would then be graphed on the existing graphs (which would require a degree of modification).


Hopefully I have explained things as clearly as possible, however if there is something I have missed or remains unexplained please let me know


Cheers


Shaun

 

[SirJB7]

Hi, Shaun!

Today is a little bit complicated journey, in my country the second and last working day of the week (some demagogue genius that happens to rule our nation's destiny for a second-4-year period decided to celebrate the unfortunate day of the Malvinas Islands' invasion in 1982 -aka Falklands- and set April 2nd as non-working day), so I'll be giving a detailed look to your last post on Thursday or Friday.

Regards!