Calculate On-Balance Volume in Excel

Learn how to calculate on-balance volume with Excel. This technical indicator employs volume flow to gauge where the smart money is going.

Get a free web-connected spreadsheet from the link at the bottom of this post, or read on to learn how it is calculated with VBA.

Indicators like RSI, ATR or MACD lag behind prices – they describe price action that has already occurred.

However, on-balance volume (OBV) is different – its aim is to lead price movements by detecting hidden buying or selling pressure.

First presented in the 1963 book Granville’s New Key to Stock Market Profits, OBV is just a running total of trading volume.

  • If close price of day n > close price of day n-1, then OBV of day n = OBV of day n-1 + trading volume
  • If close price of day n <  close price of day n-1, then OBV of day n = OBV of day n-1 – trading volume
  • If close price of day n = close price of day n-1, then OBV of day n = OBV of day n-1

This, for example is a plot of OBV for Exxon Mobil for the year from October 2013.

A plot of on-balance volume for Exxon Mobil (XOM) from 27th October 2013 to 16th October 2014

If a price follows the market trend, then OBV will move up or down with the price. However, if price and OBV diverge, then a price reversal may be looming. Since OBV leads price, a divergence means investors have to be vigilant.

Calculating OBV is simple – you just need historical close prices and trading volume, Excel and a touch of motivation.

First acquire your historical market data (including the trading volume) – use the bulk stock quote downloader to quickly download prices. Then code up the algorithm above. You only need two nested IF() statements.

Assuming your historical prices are in column E, trading volume in column F (both in data ascending order), and you want OBV in columm H, then

  • the OBV of day 1 is the trading volume of day 1
  • the OBV of day 2 is given by the formula =IF(E3 = E2, H2, IF(E3 > E2, H2 + F3, H2 – F3 ))
  • the OBV of day 3 is given by the formula =IF(E4 =E3, H3, IF(E4 > E3, H3 + F4, H3 – F4 ))
  • and so on

An illustration is given below.
An Excel formula to calculate On Balance Volume

If you’re familiar with VBA, you can write a macro that automatically generates these equations for the entire range of dates. Examine the code snippet below.

dataSheet.Range("H2").FormulaR1C1 = "= RC[-2]"
 
dataSheet.Range("H3:H" & nRows).FormulaR1C1 = _
"=IF(RC[-3] = R[-1]C[-3], R[-1]C, IF(RC[-3] > R[-1]C[-3], R[-1]C[0] + RC[-2], R[-1]C[0] - RC[-2] ))"

In this code, we fill column H (from row 2 to row nRows on sheet dataSheet) with the formulas that calculate OBV. The formulas are in R1C1 format.

A spreadsheet that downloads historical stock quotes from Yahoo Finance and plots on-balance volume (OBV)

This web-connected Excel spreadsheet will plot OBV for any ticker from Yahoo Finance. Simply

  • type in two dates
  • enter a ticker
  • and click a button

VBA provides the engine that powers the worksheet; the code connects to Yahoo Finance to download historical market data, automatically insert the formula required to calculation OBV into the spreadsheet, and generate a plot. Feel free to view and learn from the code.

Click Here to Get the Spreadsheet


9 thoughts on “Calculate On-Balance Volume in Excel

  1. Great work, Just wondering where In excel can I see where the data is being drawn from. I am trying to learn the code myself and understand how this process works.

    Appreciate where your doing here,
    – Mike

  2. Thanks for all your great work. It’s very educational. Unfortunately while it says “feel free to view and learn from the code”, the spreadsheet is password protected. Any chance of getting the password or a spreadsheet that’s not protected?

  3. Thank you for this! Is it possible to save the size and style of the displayed chart differently? Everytime I enter a new ticker, the size and style always reverts back to the earliest one, which I find to be hard on my eyes.

  4. How we can find divergence
    is it like for E.g If price has fallen down change in price % is 4.2% and change in OBV % is -3044%
    then in this case price will fall down am I right

    And one more question what is the minimum % for change in price and change in OBV for divergence

Leave a Reply to Mike G Cancel reply

Your email address will not be published. Required fields are marked *

What is 2 + 7 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)