On Balance Volume(OBV) Indicator Excel Sheet

On Balance Volume (OBV) is a very popular momentum indicator that tracks absolute change in volume to speculate price movement. It’s a mass belief that volume drives price due to aggressive trades from institutional investors. On Balance Volume indicator essentially tracks this smart money flow into the market and signals future price direction. OBV indicator was developed by Joseph Granville and introduced it in his book, “Granville’s New Key to Stock Market Profits”. In this post we would understand OBV applications and its calculation steps, there is an real time excel sheet attached at the end of post which calculates OBV for any given stock symbol.

Check out some of our very popular Excel based trading systems in the below link:

On Balance Volume (OBV) Calculation

OBV is calculated based on current and previous Close and Volume values.

• Initial OBV of the price series is equal to absolute volume.
• If current price is greater than previous price, then current OBV= previous OBV+current volume
• If current price is less than previous price, then current OBV= previous OBV-current volume
• If current price is equal to previous price, then current OBV= previous OBV

Theory Behind On Balance Volume (OBV)

As stated earlier OBV tracks smart money flow in the market. Breaking it down further, if absolute volume for a particular period increases or decreases sharply without any significant change in price, then it indicates that the price is going to move shortly. Sharp increase in volume without significant price change signals bullish move, while sharp decrease in volume without significant price change signals bearish move. This theory is backed upon assumption that institutional investors are either buying or selling in bulk when volume changes suddenly. While retail investors are taking positions opposite to institutional investors due to which volume change doesn’t reflect in price movement instantly. Eventually volume would drive the price either upwards or downwards.

OBV Interpretation

OBV can be interpreted in the following ways:

1. If change in OBV value is relatively higher than change in price for a given time period, then there may be sharp upside or downside in the price in near future. We have included columns in the Excel sheet which auto calculates change in OBV and change in price.
2. OBV Divergences can be quite handy and reliable too. A Bullish divergence is formed when OBV forms higher high and higher low, while price forms lower low. Similarly Bearish divergence is formed when OBV forms lower high and lower low while price forms higher high.

OBV can be very useful for your trading decisions when used with other price action patterns.

On Balance Volume Excel Sheet Overview

This is an semi-automated Excel sheet which updates by itself on a single click. You would just need to enter the stock name, exchange and timeframe and the Excel sheet would do the job for you. The excel calculates OBV , change in OBV (%) and change in price (%)

See the below screenshots:

How to use this Excel Sheet

Step 1: Download the Excel file from the end of this post.

Step 2: Open this Excel file and make sure you are connected to internet. Please accept if it asks to enable Macros and Data connections.

Step 3: Input the Symbol Name, Exchange Name, Interval and Number of Days.

Step 4: Click on Get Data button. The data would be automatically downloaded and OBV values would be caluclated. This data can be downloaded for max 15 days and the minimum interval is 1 minute.

OBV Excel Sheet

Related Posts

1. chandru

Dear sir

i have gone thru ur OBC excel sheet and it not working since all macros are disables..

with thanks
sschandru
9677120944

Hi Chandru,

You will have to enable Macros after you open the excel sheet

2. Rohit

Hello, I want to get details of all stocks atleast NSE 500 ,only between 2.30 to 3.30 with OBV details ,Could you please help me in doing it

3. Vaibhav

How we can find exact buy and sell using OBV
and what is the minimum % for divergence

Hi Vaibhav,

If change in OBV value is relatively higher than change in price for a given time period, then there may be sharp upside or downside in the price in near future. We have included columns in the Excel sheet which auto calculates change in OBV and change in price.

• vaibhav

kindly give some example u mean change in obv value should be higher than change in price – then we should buy or change in obv value is in negative than than in price – then we should sell is it like that

Please give some example if change in obv value is so and so and and change in price is so and so then we should do buy or sell

• VAIBHAV

KINDLY GIVE EXAMPLES

4. Bhupendra

Hi, this is indeed a good work, However the sheet is not working properly it is giving the vague values for different time. please resolve

Hi Bhupendra,

Have you downloaded the updated Excel sheet? There were few errors due to change in API URL, that has been fixed now

• Shah

Appreciated your work and your mind to give it free for others.

There is one error while loading data (Eg: Input in order-KIRLOSENG,NSE,15,5), some values are formatted as number which resulted in incorrect value.
Using below line before query querytable command will resolve the issue.
DataSheet.Range(“A:A”).NumberFormat = “@”

Thanks Shah. We will try and implement this

5. Manikandan

Hi can i use this for commodity markets?

Hi Manikandan,

For commodity markets, you would need to enter prices manually in the sheet. There is no automation available as of now.

6. VAIBHAV

DO U HAVE CRUDE OR GOLD OBV EXCEL FILE

7. kumar

nice sheet. thanks.

8. vaibhav

we are getting Run time error 13 “Type Mismatch” error please rectify as soon as possible

9. vaibhav

Not running obv file Run time erro 13

10. Nishant

Dear Sir,
Google finance not returning the stock quotes in standard format. They are returning we page rather than.

Thanks,

Hi Nishant,

We are yet to find a alternate solution. Will keep you posted

• vaibhav

when it will start sir

Hi Vaibhav,

Unfortunately we do not have a ETA. We are dependent on Google for this.

• vaibhav

Sir it was very good excel file
But it will start or not any chances and this ETA we can get or not

• vaibhav

Can you get this data from NSE earlier file was 1 hr you can do it on daily timeframe from NSE

11. vaibhav

51.95 52.35 50.45 50.6 8490892 -6414351 -2.69% -409%
In the above example if obv is -409% then if it breaks high then buy and if it breaks low then short is that so.

12. Bhupendra Pandey

Hi,
The sheet is not working. it shows the below error,
Run-time error ‘1004’

Hi Bhupendra,

Unfortunately google has discontinued its intraday data API since Aug 1st, due to which the excel sheet is unable to fetch realtime data. We are working on a alternate solution and will keep you updated. Thanks for your patience.

I found an alternative way to fetch the real time intra day data using Yahoo API. Just use below URL it is returning data in JSON format.

https://query1.finance.yahoo.com/v8/finance/chart/SBIN.NS?range=1d&interval=1m

Here we can replace SBIN.NS with any other stock scrip,
range could be like:
Days:
1d,2d,….
Months:
1mo,2mo….
Year:
1y,2y…..

Others:
ytd, max

Valid intervals: [1m, 2m, 5m, 15m, 30m, 60m, 90m, 1h, 1d, 5d, 1wk, 1mo, 3mo]

Thanks,
Nishant

Please also refer below JSON parsers useful for VBA:

Thanks,
Nishant

15. Felix

A scenario:

Say, a bullish candle has narrow spread and high volume. It was preceded by a bearish candle with narrow spread body with an above avg. volume. Proceeded by bullish narrow spread, near avg. volume candle. OBV falls sharply..then flattens out.

Question: Do you anticipate a future price rise or a fall? Why?

16. Jay

Hi, I get VB error for this line

.Refresh BackgroundQuery:=False

Any suggestions on how ppl are fixing it ?

17. Omveer Singh

Respected Sir,

As you’re also aware that google finance is not able to pick the data now days so this sheet it not working.