Using Excel to Calculate the OBV Indicator

On Balance Volume (OBV) is considered one of the volume indicators. Admittedly, volume indicators not my specialty. However, for the sake of giving it a try, I went ahead an calculated the OBV indicator. Interestingly enough it is very simple to calculate in Excel.

calculation

c = today's closing price
p = previous day's closing price
v = today's volume

if c > p then,
OBV = OBV previous day + v

else if c < p then,
OBV = OBV previous day - v

otherwise
OBV = OBV previous day 

The calculation of the OBV indicator is very much a piecewise function (for the math geeks).

calculating in excel

Download OBV Spreadsheet

After downloading the spreadsheet you will notice several columns. The highlighted columns are non-calculated columns. In this example I’ll use an RSI(14)

1. The first seven columns are data columns. I typically get this information from http://finance.yahoo.com (if you are unsure how to get this information, see this article):
   
2. The next data cell 14 is simply there to indicate how many periods n number of days in our RSI(n) function. You can play around with these numbers to see different calculations. The remaining columns are calculated from the data columns.
   
3. VERY IMPORTANT: you should know that the first row of calculated values is not calculated. It is set to zeros. This is because the formula relies on a previous value. Since we have no previous value, I set the default to zero. This means the first 20 or so rows are not very accurate.

conclusion

Hopefully this is helpful in your studies. Again, I haven’t been fond of volume indicators. As I have stated before in previous posts, indicators haven’t proven successful for me in my strategies. I would be curious however, if anyone has found success in volume indicators, especially the OBV indicator. Let us know in the comments section if you’ve had any success.

Using Excel to Calculate the MACD Indicator

Moving Average Convergence Divergence (MACD) is one of the essential indicators every budding analyst should be familiar with. If I use any indicators in my strategy I usually begin with MACD as a starting point in conjunction. Word of caution, never use any single indicator as the only analytic tool in your strategic repertoire.

calculation

c = closing value of n_day
SMA(n_day) = n_day average of closing values
M(n_day) = (2 / (n + 1))
EMA(n_day) = (c - EMA(previous day)) * M(n_day) + EMA(previous day)

MACD(n, m) = EMA(n_day) - EMA(m_day)

Just a note, whenever I try to learn an indicator, I typically try to look at several sources to get a more complete understanding. This is usually because I have questions beyond what a single source can give.

calculating in excel

Download MACD Spreadsheet

After downloading the spreadsheet you will notice several columns. The highlighted columns are non-calculated columns. In this example I’ll use a MACD(6,13)

1. The first seven columns are data columns. I typically get this information from http://finance.yahoo.com (if you are unsure how to get this information, see this article):
   
2. The next two data cells 6 and 13 are simply to indicate how many periods for the n and m parameters in our MACD(n,m) function. You can play around with these numbers to see different calculations. The remaining columns are calculated from the data columns.
   

conclusion

Hopefully this is helpful in your studies. As I have stated before in a previous post, indicators haven’t proven successful for me in my strategies. They seem helpful at least to give some ballpark indication, but I haven’t seen them work for any consistent reliability in my long term strategies. Contrary to my experience if you’ve had any success let us know in the comments section.

Using Excel to Calculate the RSI Indicator

Relative Strength Index (RSI) is considered one of the momentum indicators. When using indicators in my analysis, I never rely on the results of a single indicator. For example, I might use RSI with the MACD indicator. The reason I do this is to try and mitigate the risk that a single indicator could be wrong (see http://www.investopedia.com/articles/trading/12/using-trading-indicators-effectively.asp).

calculation

UP(n_day) = n_day average of closing values that were gains
DOWN(n_day) = n_day average of closing values that were losses
RS(n_day) = UP(n_day) / DOWN(n_day)

RSI(n_day) = 100 - 100/(1 + RS(n_day))

RSI is one of the easier calculations to show “on paper”. But it isn’t so obvious to calculate in Excel.

calculating in excel

Download RSI Spreadsheet

After downloading the spreadsheet you will notice several columns. The highlighted columns are non-calculated columns. In this example I’ll use an RSI(14)

1. The first seven columns are data columns. I typically get this information from http://finance.yahoo.com (if you are unsure how to get this information, see this article):
   
2. The next data cell 14 is simply there to indicate how many periods n number of days in our RSI(n) function. You can play around with these numbers to see different calculations. The remaining columns are calculated from the data columns.
   
3. VERY IMPORTANT: you should know that the first row of calculated values is not calculated. It is set to zeros. This is because the formula relies on a previous value. Since we have no previous value, I set the default to zero. This means the first 20 or so rows are not very accurate.

conclusion

Hopefully this is helpful in your studies. As I have stated before in a previous post, indicators haven’t proven successful for me in my strategies. There always seems to be promise when reading what other people have done. But when I look deeper into how reliable or consistant the indicators are, I am often disappointed (such as http://www.forbes.com/fdc/welcome_mjx.shtml). Contrary to my experience if you’ve had any success let us know in the comments section.

Using Indicators

There are many indicators designed to give the user some insight on when it’s good to buy into or sell a stock. Generally speaking, you want to buy at valleys and sell at peaks. Some well know indicators include, but are not limited to; MACD, SMA, EMA, Bollinger Bands, Ultimate Oscillator, RSI, Williams %R and many more.

Charting

There are various free sources for charts online, such as yahoo, google, and stockcharts. Many online stock broker services also have built in charts and tools; TDAmeritrade, Scotttrade, and ExTrade. Personally my favorite is to get a price history from yahoo, download the csv file and open it in Excel. Then play around with different indicators and strategies. See http://flamanthablog.com/?p=24 on how to do that if you’re curious. 🙂

thoughts

Typically analysts advise against using just one indicator as the only source of determining whether or not to buy or sell a stock. Thus any good strategy is to use a combination of indicators (see http://www.investopedia.com/articles/trading/12/using-trading-indicators-effectively.asp). The trick is to decide which combination and exactly which range or numerical variation of those indicators will determine or trigger in your strategy of when to buy or sell. In my experience, I haven’t seen any consistent success while using any indicator or combination of indicators. I’ve tried several indicators in back tests while switching the variables in a spreadsheet across multiple stocks. Some were successful for a particular stock at a particular time period, but the “formula” didn’t hold up when applying the strategy across the board. After a fair amount of back testing across multiple stock symbols, I typically found success to be 50/50. This tells me that my success is no better than throwing darts. However that being said, it is certainly possible that I didn’t try the right combination with the right variables. Please let us know if you’ve had any better experience then I had. I would love to hear it.

Basics of Using Excel with Yahoo Finance Historical Prices

There are a many ways to analyze stock with Excel. In this post I’ll simply show some of the basics of how I use Excel with stock analysis.

instructions

1. First go to http://finance.yahoo.com/ and enter the stock symbol into the Quote Lookup box and click the Go button. In this example I’ll be using Disney (DIS).
2. Once on the DIS summary page. Click the Historical Prices on the left menu.
   
3. On the Historical Prices page you’ll see data in a tabular form. Scroll to the bottom and click the Download to Spreadsheet link. This will prompt a download of a CSV file, which is just a universal data file type that can be opened in most spreadsheet applications.
   
4. Once I have it opened in Excel I typically like to sort the first column so that I get the oldest dates first. Do this by clicking the header (A column) at the top so that the whole column is highlighted and then select Sort Oldest to Newest.
   

So this is the basics. At this point I typically calculate various indicators and triggers for buying and selling so I can back-test my strategy to discover or explore if any of my ideas would have worked in the given periods. Also I should point out that I found it useful to chart some of my calculations using the chart tools in Excel. That was helpful because it gives me a visual perspective of the strategy. Viewing data in various ways can help you creatively explore and modify your strategy. Good luck! I hope this is helpful to you to get you started.

Launching flamanthablog!

I’m excited to announce the launching of flamanthablog.com!

who am i?

To begin I should say that my name is Bryan Heathcote. I am a software engineer by trade and enjoy analyzing the stock market on my own time. By no means do I claim to be a expert in the stock market, but at least I can share some of my fun and adventures in stock market analysis.

what is flamanthablog?

As I already stated, my aim is to share my thoughts and experiences in stock market analysis. In turn, I hope to begin a discussion and hear your input and wisdom on the subject. If I’m wrong, fantastic. Hopefully together we can gain a greater understanding of the market and how it operates for smarter investments for our lives and families.