How to Calculate On-Balance Volume in Excel
On balance Volume has been a reliable indicator for traders
for a long time.
It was developed in 1960 by Joseph Granville.
The indicator has stood the test of time and still used
pretty reliably.
The underlying theory of the indicator is simple.
Volume precedes the price. The creator of the indicator
believed that any price movement in the stock are first reflected in the volume
movement.
The method for OBV is as follows:
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
Calculating OBV is simple – you just need historical close
prices and trading volume, Excel and a touch of motivation.
Assuming your historical prices are in column E, trading
volume in column F (both in data ascending order), and you want OBV in column
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
A summary is as given below for stock ticker MSFT
