Pulling data from website

**inoexcel** · 07-28-2007, 03:02 AM

Hi,

Is there a way where I can pull data from google finance through excel?

For example, I type in a ticker symbol of a stock in cell A1 (say F for Ford), and want A3 to show pull over the income statement (http://finance.google.com/finance?fstype=ii&q=F) and A50 to pull over balance sheet (http://finance.google.com/finance?fstype=bi&q=F) .

Thanks.

**mudraker** · 07-28-2007, 04:00 AM

With a macro - You just need to decide how to trigger the macro to run

Some options are
From a button
When you change the value of A1

Sub Macro1()
   Dim sTS As String
   
   sTS = Range("a1").Value
   With ActiveSheet.QueryTables.Add(Connection:= _
      "URL;http://finance.google.com/finance?fstype=ii&q=" _
         & sTS, Destination:=Range("A3"))
      .Name = "finance?fstype=ii&q=" & sTS
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlOverwriteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .WebSelectionType = xlSpecifiedTables
      .WebFormatting = xlWebFormattingNone
      .WebTables = "2,3"
      .WebPreFormattedTextToColumns = True
      .WebConsecutiveDelimitersAsOne = True
      .WebSingleBlockTextImport = False
      .WebDisableDateRecognition = False
      .WebDisableRedirections = False
      .Refresh BackgroundQuery:=False
   End With

   With ActiveSheet.QueryTables.Add(Connection:= _
      "URL;http://finance.google.com/finance?fstype=bi&q=" _
         & sTS, Destination:=Range("A50"))
      .Name = "finance?fstype=bi&q=" & sTS
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlOverwriteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .WebSelectionType = xlSpecifiedTables
      .WebFormatting = xlWebFormattingNone
      .WebTables = "2,5"
      .WebPreFormattedTextToColumns = True
      .WebConsecutiveDelimitersAsOne = True
      .WebSingleBlockTextImport = False
      .WebDisableDateRecognition = False
      .WebDisableRedirections = False
      .Refresh BackgroundQuery:=False
   End With
End Sub

**inoexcel** · 07-28-2007, 06:10 PM

Thanks Mudraker. That was very helpful.

I'm pretty new to this macro business, and for the most part, I think I understand what was written down. Some follow up questions if I may:

Going back to the ford income statement example, (http://finance.google.com/finance?fstype=ii&q=F), google allows the user to switch between Quarterly data and Annual data. However, the website address remains the same.

If I wanted to tell excel to grab the annual data as opposed to the quarterly data, which part of the macro do I code this in? Is it related to the web table?

On websites like these, where you can switch between data without the website address changing, how do tell excel which link to pull the data from.

Thanks again.