What VBA script is needed so a web query looks for a value existing in a cell?

[JulioMaz]

I wrote a query to download stock data from the web which works when I enter the symbol manually. However, I tried making the query to look for the symbol in a cell but can't fire it out. Any direction would be greatly appreciated. The code has more lines but for simplicity I copied below is the part of the VBA code that deals with this issue.

Dim qurl As String
Dim r As Integer
'
qurl = "http://finance.yahoo.com/q/ae?s=" & Cells(r, 15)
'
'
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=ActiveSheet.Range("B4"))
.PostText = "Yahoo_Analyst_Estimates Dynamic"
.Name = False
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.HasAutoFormat = True
.RefreshOnFileOpen = 0
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.SaveData = True
.Refresh BackgroundQuery:=False
.UseListObject = False
End With

 

[JulioMaz]

I got it to work by eliminating the ".PostText" statement and making a few other minor changes.

For those who are new to this post:

The purpose of this VBA program is to download stock financial data from yahoo to an excel sheet, and copy selected items of the downloaded data to a separate row for each stock. The search and download is dynamic and provides the capability of entering, retrieving and organizing data for several symbols at once. And Yes. It works on Macs.

- The macro works on the active sheet but can be easily adapted.
- The symbols are on column "O" and the beginning row number (or First Row) is manually entered.
- The program downloads Table-Only from three different websites onto the active sheet via three queries.
- When all three queries for the first stock are downloaded, the selected information is copied to a row next to the stock symbol.
- Then, the loop selects the next stock symbol.
- Before downloading the data for the next stock all previous data is cleared.

Below is the final code

Sub Yahoo_Metrics_iMac7()
'
' Yahoo_Metrics_iMac7 Macro
'
Dim qurl As String
Dim r As Integer    'Row number for each symbol on columne O
Dim fr As Integer
Dim lr As Integer
Dim Symbol As String
 
'
lr = ActiveSheet.Cells(Rows.Count, "O").End(xlUp).Row   'Sets last row when there are no more symbols
fr = ActiveSheet.Cells(1, "C")  'Sets the first row number. This allows me to change the first symbol on the list
'
For r = fr To lr
    Range("B4:F68").ClearContents 'clear previous
    Range("B70: j131").ClearContents 'clear previous"
    Range("B133:g205").ClearContents 'clear previous
'
Symbol = ActiveSheet.Cells(r, "O")
ActiveSheet.Cells(1, 4) = Symbol
qurl = "http://finance.yahoo.com/q/ae?s=" & Symbol
   
    '
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=ActiveSheet.Range("B4"))
        .Name = False
        .FieldNames = False
        .RefreshStyle = xlOverwriteCells
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .HasAutoFormat = True
        .RefreshOnFileOpen = 0
        .BackgroundQuery = False
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .UseListObject = False
        .SaveData = True
    End With
 
qurl = "http://finance.yahoo.com/q/ao?s=" & Symbol
 
 With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=ActiveSheet.Range("B71"))
        .Name = False
        .FieldNames = False
        .RefreshStyle = xlOverwriteCells
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .HasAutoFormat = True
        .RefreshOnFileOpen = 0
        .BackgroundQuery = False
        .TablesOnlyFromHTML = True
        .SaveData = True
        .Refresh BackgroundQuery:=False
        .UseListObject = False
    End With
   
qurl = "http://finance.yahoo.com/q/ks?s=" & Symbol
  
     With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=ActiveSheet.Range("B133"))
        .Name = False
        .FieldNames = False
        .RefreshStyle = xlOverwriteCells
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .HasAutoFormat = True
        .RefreshOnFileOpen = 0
        .BackgroundQuery = False
        .TablesOnlyFromHTML = True
        .SaveData = True
        .Refresh BackgroundQuery:=False
        .UseListObject = False
    End With
    Cells.Select
    With Selection
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    With ActiveSheet
        .Cells(r, 16) = .Range("C57")
        .Cells(r, 17) = .Range("D57")
        .Cells(r, 18) = .Range("F57")
        .Cells(r, 19) = .Range("C58")
        .Cells(r, 20) = .Range("C59")
        .Cells(r, 21) = .Range("D59")
        .Cells(r, 22) = .Range("F59")
        .Cells(r, 23) = .Range("G89")
        .Cells(r, 24) = .Range("G92")
        .Cells(r, 25) = .Range("C145")
        .Cells(r, 26) = .Range("C148")
        .Cells(r, 27) = .Range("C149")
        .Cells(r, 28) = .Range("C174")
        .Cells(r, 29) = .Range("C177")
        .Cells(r, 30) = .Range("G157")
        .Cells(r, 31) = .Range("G158")
        .Cells(r, 32) = .Range("G161")
    End With
Next r
End Sub