Collect historical data from Yahoo Finance in Excel VBA

Many professional analysts and traders use Yahoo Finance as a tool to find historical financial data.  By using Excel, we can download the data from Yahoo Finance and save it to Excel file.  We can get some data such as historical daily prices in the past period and the basic information about annual balance sheet.  With the financial data, you may start doing your financial model on valuation, technical analysis, and even algorithmic trading program.

Here are some simple steps in order to get financial data from Yahoo Finance:

1) Get to know about ticker number of the stock that you are looking for.

2) identify what kind of financial data you are going to use for analysis.

3) Adjust the code of VBA according to what you need

We will use make a simple page in Excel file and make such arrangements for putting corresponding VBA codes.

 

Cell D4 corresponds to the variable called StartDate.

Cell D5 corresponds to the variable called EndDate.

Then you just need to use the query function in Excel VBA to download data from Yahoo Finance.

Sub collectdata()
    Dim DataSheet As Worksheet
    Dim EndDate As Date
    Dim StartDate As Date
    Dim ticker As String
    Dim yahoourl As String
    Dim nQuery As Name
    Dim LastRow As Integer
    
''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Set DataSheet = ActiveSheet
  
        StartDate = DataSheet.Range("startdate").Value
        EndDate = DataSheet.Range("enddate").Value
        ticker = DataSheet.Range("ticker").Value
        
        Sheets("Data").Range("a1").CurrentRegion.ClearContents
'''''This is the link that we grab information from Yahoo Finance       
'''''http://ichart.finance.yahoo.com/table.csv?s=NQ&d=0&e=28&f=2010&g=d&a=3&b=12&c=2009&ignore=.csv
'''''http://real-chart.finance.yahoo.com/table.csv?s=NQ&a=10&b=11&c=2014&d=10&e=24&f=2014&g=d&ignore=.csv
'''''s->ticker
'''''a->start month -1
'''''b->start day(two digits)
'''''c->start year
'''''d->end month -1
'''''e->end day(two digits)
'''''f->end year
'''''g->d for daily, m for monthly, y for yearly
        
  yahoourl = "http://ichart.finance.yahoo.com/table.csv?s=" & ticker _
    & "&a=" & Month(StartDate) - 1 _
    & "&b=" & Day(StartDate) _
    & "&c=" & Year(StartDate) _
    & "&d=" & Month(EndDate) - 1 _
    & "&e=" & Day(EndDate) _
    & "&f=" & Year(EndDate) _
    & "&g=d" _
    & "&ignore=.csv"

Here are some important partial VBA codes about getting information from the Yahoo Finance page and saving in the Excel File.

It is better for you to know about each parameter’s meaning of the link of Yahoo Finance so that you will be familiar to change the code own your on.

You can click “Add to cart” button below and leave a comment to us. Remember to put your e-mail in your comment.

We will send you the complete Excel file if needed. : )

In the next post, we will introduce how to do the basic financial analysis with the data you got in Excel.

---

[wp_cart_button name=”Excel VBA – Collect Data From Yahoo Finance” price=”5″]

[show_wp_shopping_cart]

---