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:
https://www.google.com/finance?q=NASDAQ%3AMSFTThe page «Historicalprices» contains the reference for data downloading in the format .CSV:
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