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.
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.
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.
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 + RC[-2], R[-1]C - 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.
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.