Smart investors know that the key to choosing which stock to buy or sell is a proper analysis of every stock’s price history.
Even if you have heard rumours about a particular company that imply its explosive future growth or its imminent demise, these rumours would have already left their footprint on that company’s traded stock price. Market is as fast – if not faster – than rumours! It is therefore important that you have access to both real time and historical data of all those stocks that fall under your radar screen.
In my previous article about live feeds in Excel I explained how you may get access to such feeds. In the current article I will confront the question about how you may analyse the historical data of several stocks in order to pick up the one with the best future prospects.
Qualivian Investment Partners performance update for the month ended July 31, 2022. Q2 2022 hedge fund letters, conferences and more Dear Friends of the Fund, Please find our July 2022 performance report below for your review. Qualivian reached its four year track record in December 2021. We are actively weighing investment proposals. Starting in November Read More
You might have heard that professional traders go one step further and apply some sort of mathematical transformation on the raw stock prices to produce a time series of numbers, called technical indicators. Many online brokers even supply you with ready-made popular indicators, like Moving Average, MACD (Moving Average Convergence Divergence), RSI (Relative Strength Index) and OBV (On-Balance Volume).
If you think these tools could help you make a profit, think again! These indicators are so commonplace that whatever they imply for the stock’s prospects is already priced in the quoted price before you can blink your eye! In other words, if they convey a positive signal, the market will swallow enough buy orders to raise the stock price to a level that is not still a bargain by the time you look at this signal!
Because professional traders know this fact, they use their own custom technical indicators either by devising unique mathematical algorithms or tweaking those already in place.
Guess what? You need to do the same! Not of course by solving differential equations, but by building your own custom analysis in Excel. All you need is some sort of utility that can dump the raw numbers in your spreadsheet. Then you could make yourself a cup of coffee to boost your imagination, raise your sleeves and beat everybody else! You can do it because Excel puts you in front of the steering wheel and lets you manipulate the data in any way you want.
As a practical and very simple example, imagine you want to compare the past performance of Microsoft vs Google. Most trading tools would easily display two separate price history charts, one for Microsoft (ticker MSFT) and one for Google (ticker GOOG) that could look like this:
Great! Right? But still, wiser have you not become!
What you really want, is having two curves plotted against each other on the same space. Rather than relying on broker toolkits, how about plotting the chart yourself? All you need is having two columns of prices in an Excel spreadsheet and create a two-curve chart out of them.
Getting the MSFT Historical Data in Excel
While you can easily download the historical prices from a site such as Yahoo Finance, I would advise you to use the Excel Deriscope Add-In because a) is made by me :-) b) comes with an integrated wizard that greatly assists you with the analysis of market data in the spreadsheet c) supports several live feeds providers d) is locally installed (not SAAS), which means nobody can spy on your data and e) is practically free with a very low one-off price for lifetime ownership.
The following 20-seconds video shows how you may use the wizard to create the spreadsheet formulas that return the historical Microsoft prices from Yahoo Finance:
As you see in the image below, the wizard has created exactly two formulas:
The first formula in cell A2 is =ds(A3:B18) and returns the text &HistYF_A2:1.2 that is a unique label of an object that contains the specifications of your historical data request to be sent to Yahoo’s server.
The second formula in cell A1 is =dsLive(A2) and does the real job: It contacts Yahoo’s server with the request defined in its input argument A2 and returns the text &Variant_A1:1.1, which uniquely identifies the object that contains the received feeds.
The wizard’s taskpane on the right always displays the contents of the object associated with the currently selected cell. In the case here, cell A1 is selected and therefore the wizard displays the feeds (MSFT historical data) received from Yahoo Finance in tabular form consisting of 7 columns:
Getting both the MSFT and GOOG Historical Data in Excel
Before showing you how to place the received feeds in the spreadsheet grid so that you can work with them, let us try to add a second ticker symbol in the request object defined in cell A2.
Below you see one possible method that can be readily extended to handle many more additional ticker symbols. The ticker symbols (here MSFT and GOOG) are entered under the Symbols= key in the yellow highlighted rows.
As before, the request is sent to Yahoo’s server through the formula =dsLive(A21) in cell A20 and the wizard displays the received feeds because cell A20 is currently selected. Due to the existence of two tickers, the output table now consists of 13 columns: The date column plus 6 columns per ticker.
Keeping only the Adjusted Close Columns
Chances are that you don’t want to mess with all these columns, especially if more than two stocks are involved. In most cases you wo