Rajandran R Telecom Engineer turned Full-time Derivative Trader. Mostly Trading Nifty, Banknifty, USDINR and High Liquid Stock Derivatives. Trading the Markets Since 2006 onwards. Using Market Profile and Orderflow for more than a decade. Designed and published 100+ open source trading systems on various trading tools. Strongly believe that market understanding and robust trading frameworks are the key to the trading success. Writing about Markets, Trading System Design, Market Sentiment, Trading Softwares & Trading Nuances since 2007 onwards. Author of Marketcalls.in)

VBA Macro to Download Bhavcopy

1 min read

Message from Rajib

Hi Rajandran,

Thanks a ton!

It seems to be a big problem if these things don’t get fixed. All the indices are out of my charting software!

I can manage to write a VBA that will collate individual files and write it in the CSV. But I need something that will download the EOD text files for these indices into once place! That too me is more tedious than writing it on to the csv file.

Regards,
Rajib

Here is a initiative from me to automate the NSE EOD data in ASCII format using simple VBA Scipts in MS Excel. The following MS Excel VBA macro copies the Getbhavcopy zip file corresponding to 02Nov2010 from NSE India portal into your local drive(c:eoddata) and then extracts the Zip file and shows you the content in the csv file.

This is one of the primary requirment to develop the macro further. And for me its works fine and few more steps needs to be taken to design a Getbhavcopy Ascii
format downloader using MS EXCEL. And as of now its not for commercial use. The macro can be used only for testing/development purpose
[wp_ad_camp_5]

 
Pre-Requisite to run the macro
1)Create a Folder Name eoddata in C drive
2)Make Sure that you had added the Reference xzip.dll which is used to zip or unzip files
3)To install X-Zip.dll reference please visit and read the X-Zip.dll I.e X Standard – Zip 2.5 installation instructions

4)Make Sure that you had enabled the correct Macro security settings.

5)Download the MS Excel Containing the Macro

4)Go to Tools->Macro and Run the EODData Macro


[x-zip reference]

Here is the VBA Macro Code

Private Declare Function URLDownloadToFile Lib “urlmon” Alias _
“URLDownloadToFileA” (ByVal pCaller As Long, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function

Sub eoddata()
Dim sURL As String
Dim LocalFilename As String
Dim filename As String
Dim objZip

Const UNC = “C:eoddata”
fname = “cm02NOV2010bhav.csv”
zipfilename = fname & “.zip”

sURL = “http://nseindia.com/content/historical/EQUITIES/2010/NOV/” & zipfilename
LocalFilename = UNC & zipfilename

Debug.Print DownloadFile(sURL, LocalFilename)

Set objZip = CreateObject(“XStandard.Zip”)
objZip.UnPack LocalFilename, UNC
Set objZip = Nothing
Workbooks.Open filename:=UNC & fname

End Sub

Hope the complete solution will be soon available in our blog with free and open source VBA codes 🙂

Rajandran R Telecom Engineer turned Full-time Derivative Trader. Mostly Trading Nifty, Banknifty, USDINR and High Liquid Stock Derivatives. Trading the Markets Since 2006 onwards. Using Market Profile and Orderflow for more than a decade. Designed and published 100+ open source trading systems on various trading tools. Strongly believe that market understanding and robust trading frameworks are the key to the trading success. Writing about Markets, Trading System Design, Market Sentiment, Trading Softwares & Trading Nuances since 2007 onwards. Author of Marketcalls.in)

How Geopolitical Tensions Impact Stock Markets: A Closer Look

Geopolitical tensions have always had a significant influence on the global economy, particularly on stock markets. Investors, both individual and institutional, closely monitor these...
Rajandran R
2 min read

What Traders/Investors need to know about Hypodermic Needle Theory

The Hypodermic Needle Theory, also known as the Magic Bullet Theory, is a communication theory that suggests that media messages are injected directly into...
Rajandran R
2 min read

How to Get Futures and Options Data and Commodities…

In this blog, we will explore how to use Algomojo to get futures and options data and commodities data into Google Spreadsheet, step by...
Rajandran R
4 min read

12 Replies to “VBA Macro to Download Bhavcopy”

  1. @ Rajendran,

    Ooops … was busy in coding it … did not see this message and posted in the earlier post itself.

    I have got it provided the text files are available to read. Assuming “Data Downloader” is working and volume problem will be sorted out soon … we can avoid the manual intervention. Even the indices format difference with GetBhavCopy is taken care of.

    Let me know how I can share it.

    Regards,
    Rajib

  2. sir ,
    i was going through nifty fut stocks on nma chart but could not find a single stock with desired results on daily as well as weekly basis , what should be the parameters if i want to go for a medium term pick , like the one picked by you few days back (ahmedabad…).
    tx

  3. Hello Sir,

    Thanks a lot for VBA Script, but m looking for a script to download weekly NSE Futures Data. Is it possible to get weekly NSE Futures data through script or from anywhere…plz suggets

    Regards,
    TT

  4. I have downloaded bhav copies from last 1 year. Now I want these scrips in weekly format. May I request you to help me out

    1. Hi,

      Its a old article and its a temporary solution available at that time and NSE has changed its download URL . Now try with Getbhavcopy EOD downloader to get ASCII files.

Leave a Reply

Get Notifications, Alerts on Market Updates, Trading Tools, Automation & More