Using Excel and VBA

[Tenspeed39355]

Hi guys. I have another one for you. I need to extract some data from a web site and
insert it in excell ss. To start there will be 650 stock symbols in column A.
Copy the first symbol and insert it at http://www.cefconnect.com. After the screen shows
up for AWF which will be the first symbol scroll down the screen until you come to
the heading LEVERAGE. Copy line three (3) Total Debt (USD): $14.090M. Then insert
line three in the Excell ss at E3. Then repeat the process again until all 650 symbol have the data beside them. Here is an example of some of the symbols. AWF,EHI,EMD,HYB
KST,PHT,PHF, HIX,ACG,JDD. If you can get me to this point I think I will be able to
change some of the lines and extract the rest of the data. I think. LOL. Thanks again
for all the help you have given me in the past. I do not know how you do request as
quickly as you do but I do appreate it.
Max

[Shred Dude]

UDF to strip Debt as requested. Use as formula in Cell B1 for Symbol in Cell A1:

[VBA]=getDebt(A1)[/VBA]

Code:

[VBA]'VBA Express Question: http://www.vbaexpress.com/forum/showthread.php?t=35778
'20 Jan 2011
'Written by Shred Dude
'
'

Public Function getDebt(Symbol As String) As String
Dim xmlhttp As Object
Dim strHTML As String
Dim strURL As String

strURL = "http://www.cefconnect.com/Details/Summary.aspx?ticker=" & Trim(Symbol)

Set xmlhttp = CreateObject("msxml2.xmlhttp")
With xmlhttp
.Open "Get", strURL, False
.send
strHTML = .responsetext

End With

getDebt = parseDebt(strHTML)

End Function

Public Function parseDebt(s As String) As String
Dim StartPnt As Long
Dim EndPnt As Long
Dim strID As String

strID = "ctl00_contents_SummaryContainer_BasicsTab_ucFundBasics_dvFB1_TotalBorrowin gsHeader"

On Error GoTo errExit

StartPnt = InStr(1, s, strID)
StartPnt = InStr(StartPnt, s, "<td")
StartPnt = InStr(StartPnt, s, ">") + 1
EndPnt = InStr(StartPnt, s, "</")

parseDebt = Mid(s, StartPnt, EndPnt - StartPnt)

'Some Symbols do not show an Amount on the total Debt Line item...
If parseDebt = "" Then parseDebt = "Blank"

Exit Function

errExit:

parseDebt = "Error"

End Function
[/VBA]

Workbook attached. Change strID as needed to parse out other line items.

[Tenspeed39355]

Hi Shred Dude. Your ss works like a charm. Good job. Can you add the following to the
TotalDebt column which is B1.
C1 add Structural Leverage (usd)
D1 add Structural Leverage (%)
E1 add Effective Leverage (usd)
F1 add Effective Leverage (%)

If you can add the columns I will be appreicative as I thought I could finish up where you
left off but it is above my head. Again thanks. I did not get an e-mail telling me you had
finished so please drop me an e-mail on this part.
Max