32

On Balance Volume(OBV) Indicator Excel Sheet

Posted on by admin

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:

Excel Based Trading Systems

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:

Excel Input

On Balance Volume Excel Sheet

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.

Download link for On Balance Volume(OBV) Excel Sheet

Please see the below link to download OBV excel Sheet. Please let us know if you have any feedback or comments.

OBV Excel Sheet

Spreadsheets for Success

Related Posts

32 Comments

  1. Dear sir

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

    please do the needful ..

    with thanks
    sschandru
    9677120944

    • Hi Chandru,

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

  2. 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. 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.

      • 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

  4. 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

      • 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 = “@”

    • Hi Manikandan,

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

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

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

    Could you please help in this regards?

    Thanks,

        • Hi Vaibhav,

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

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

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

  11. 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.
    Please clarify

  12. 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.

  13. Dear Admin Sir,

    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

  15. 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. Hi, I get VB error for this line

    .Refresh BackgroundQuery:=False

    Any suggestions on how ppl are fixing it ?

  17. 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.

Leave a Reply

Your email address will not be published.