dynamic Web Query

[Andy16H]

I have the following Web Query

WEB
1
[URL]http://finance.yahoo.com/q/pr?s=["Symbol"]+Profile[/URL]

Selection=36
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

I have played around with the .iqy file and I can not figure out how to make the query reference a cell.

Currently the above query ask where to paste datat and for a symbol and it returns the following:[TABLE="width: 336"]
<tbody>[TR]
[TD="class: xl65, width: 279, bgcolor: transparent"]Expense
[/TD]
[TD="class: xl65, width: 58, bgcolor: transparent"]IMLLX
[/TD]
[TD="class: xl65, width: 111, bgcolor: transparent"]Category Avg
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Annual Report Expense Ratio (net):
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1.25%
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0.96%
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Prospectus Net Expense Ratio:
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2.10%
[/TD]
[TD="class: xl65, bgcolor: transparent"]N/A
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Prospectus Gross Expense Ratio:
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2.10%
[/TD]
[TD="class: xl65, bgcolor: transparent"]N/A
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Max 12b1 Fee:
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1.00%
[/TD]
[TD="class: xl65, bgcolor: transparent"]N/A
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Max Front End Sales Load:
[/TD]
[TD="class: xl65, bgcolor: transparent"]N/A
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]5.28%
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Max Deferred Sales Load:
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1.00%
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2.54%
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]3 Yr Expense Projection*:
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]658
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]603
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]5 Yr Expense Projection*:
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1,129
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]921
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]10 Yr Expense Projection*:
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2,431
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1,805
[/TD]
[/TR]
</tbody>[/TABLE]

The only data I need from this table is the Max 12b1 Fee. I have a list of several hundred stock symbols in column B starting at row 10. I need the max 12b1 fee to be returned to the corresponding cell in column E.

Any tips or advice would be greatly appreciated.

 

[ZVI]

Try this UDF:

Function YahooInfo(Symbol As String, Optional Info As String = "Max 12b1 Fee:", Optional OffsetCol As Long = 1)
' ZVI:2013-08-13 http://www.mrexcel.com/forum/excel-questions/719812-dynamic-web-query.html
  Dim s As String, txt As String, Url As String
  Dim i As Long, j As Long
  Static objXmlHttp As Object
  If objXmlHttp Is Nothing Then Set objXmlHttp = CreateObject("MSXML2.XMLHTTP")
  Url = "http://finance.yahoo.com/q/pr?s=" & UCase(Symbol)
  If Len(Symbol) * Len(Info) = 0 Then Exit Function
  With objXmlHttp
    .Open "GET", Url, False
    .setRequestHeader "Accept-Encoding", "default"
    .setRequestHeader "Accept-Charset", "Windows-1252"
    .setRequestHeader "Cache-Control", "no-cache"
    .send
    txt = .responseText
  End With
  s = Info
  i = InStr(1, txt, s, vbTextCompare)
  If i Then
    i = InStr(InStr(i + Len(s), txt, "<" & "/t") + 5, txt, ">")
    j = InStr(i, txt, "<" & "/")
    Select Case OffsetCol
      Case 1
      Case 2
        i = InStr(j + 5, txt, ">")
        j = InStr(i, txt, "<" & "/")
      Case Else
        Exit Function
    End Select
    s = Trim(Replace(Mid(txt, i + 1, j - i - 1), ",", ""))
    If InStr(s, "%") Then
      YahooInfo = Val(Replace(s, "%", "")) * 0.01
    ElseIf IsNumeric(s) Then
      YahooInfo = Val(s)
    ElseIf InStr(s, ".") And IsNumeric(Replace(s, ",", "")) Then
      YahooInfo = Val(s)
    Else
      YahooInfo = s
    End If
  Else
    YahooInfo = "#Info?"
  End If
End Function

The usage:

Excel Workbook
	B	C	D	E
9	Symbol	*	*	Max 12b1 Fee:
10	IMLLX	*	*	1.00%
11	OSFDX	*	*	N/A
12	PTTDX	*	*	0.25%
13	VFINX	*	*	N/A
14	OPNYX	*	*	0.24%
15	OCMGX	*	*	0.77%
16	MXCCX	*	*	0.75%
17	FMAGX	*	*	N/A
18	FKUTX	*	*	0.15%
19	DSEFX	*	*	0.25%
20	FMXSX	*	*	0.30%
21	LEXMX	*	*	0.25%
22	RERCX	*	*	0.50%
Sheet1

 

[ZVI]

Please fix the typo,
replace this: ElseIf InStr(s, ".") And IsNumeric(Replace(s, ",", "")) Then
by that one: ElseIf InStr(s, ".") And IsNumeric(Replace(s, ".", "")) Then

 

[Andy16H]

I wish I understood what you did, but I'm afraid to ask. It worked Great. Thanks

 

[ZVI]

Glad it has helped
UDF just parses the source code of those web pages (the same type of).

You may call UDF also like this:
=YahooInfo( "IMLLX", "Max 12b1 Fee:" )
where
"IMLLX" is the symbol (tag),
"Max 12b1 Fee:" is the name of Expense in the table of the web page.

Example of getting the ratio of another expense:
=YahooInfo("IMLLX","Annual Report Expense Ratio (net):",1) - returns 1.25%
=YahooInfo("IMLLX","Annual Report Expense Ratio (net):",2) - returns 0.96%
where 1 and 2 are the column offsets of the Expense's table

Cheers!

 

[Andy16H]

I've been working through undrstanding how this works and I think i have it figured out. The only part that I'm lost on is when it "GET" data from the url and then sends it. What can I do to see how the data looks after it is sent. This would be very helpfull in letting me see what information is available and in adapting this to future websites.

 

[ZVI]

You can save .responseText to the text file for its farther analyzing. like this:

    txt = .responseText
    Dim f As String, FN As Integer
    FN = FreeFile
    f = ActiveWorkbook.Path & "\web_source.txt"
    If Len(Dir(f)) Then Kill f
    Open f For Binary Access Write As #FN
    Put #FN, , txt
    Close FN

This creates web_source.txt file in the folder where the active workbook is stored

 

[Andy16H]

To me it looks as if it is just searching the source file for "Max 12b1 Fee:" if this is the case how does it know there are 2 columns?

Also how could this work on a site like Transamerica Asset Allc Mod Gr C (IMLLX) Fund Performance and Returns as the data does not appear in the source file.

I think I understand how it works except for being curious if the source file is somehow being organized into columns?

I have modified it to look in cell A1 for the URL and I am putting what I am looking for in row 3 starting in column B. How would you adjust to make it search for multiple items at one time.
Stock Symbol in A3
B2 Annual Report Expense Ratio (net):
C2 Max 12b1 Fee:
D2 ....
E2 .....

Formula
=YahooInfo(A3,B2,C2,D2,"Column Number)
Returns the data in the appropriated cell