Stock Quotes Data from Google Finance

In the process of application development with the help of Excel program, from time to time there is an objective to import data from internet. As an example, let's consider the example of importing market data of Google Finance.

For example, we need data of stock quotation of Microsoft Corporation:

If we change the period of the desired data, then the reference will look like this:

In order to upload this file directly to Excel, use the function of data uploading from the text file Menu :: Data :: FromText, and instead of the file path, point the reference on CSV file from Google Finance.

Write VBA macros for data uploading according to three input parameters:

Sub get_google_finance_data(indx As String, st As Date, en As Date)

• indx - string variable with the name of the company (NASDAQ:MSFT)
• st - date of period starting
• en - date of period ending

1. Transform the dates to text variable (s1 and s2):

s1 = Choose(Month(st),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
s1 = s1 & "+" & Trim(CStr(Day(st))) & ",+" & Trim(CStr(Year(st)))

s2 = Choose(Month(en),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
s2 = s2 & "+" &Trim(CStr(Day(en))) & ",+" & Trim(CStr(Year(en)))

2. Make up URL for uploading of .CSV file:

Dim str_query As String

str_query = "TEXT;http://www.google.com/finance/historical?q=" + Trim(UCase(indx)) _
    + "&output=csv&startdate=" & s1 & "&enddate=" & s2

3. With the help of this URL we'll get the historical stock quotation:

With ActiveSheet.QueryTables.Add(Connection:=str_query, Destination:= ActiveSheet.Range("$A$1"))
        .Name = "output=csv"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
End With

4. Sort out the data:

Activesheet.Columns("A:F").Select
Activesheet.Sort.SortFields.Clear
Activesheet.Sort.SortFields.Add Key:=Range("H2:H65000"), SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortNormal
With shtData.Sort
    .SetRange Activesheet.Range("A1:H65000")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

It's ready! Now let's make a simple interface to fill input parameters and make a chart of the received data.

Download full function template: Excel Template: Stock Quotes Data from Google Finance