7 Replies - 1047 Views - Last Post: 30 May 2013 - 05:41 AM Rate Topic: -----

#1 beachy15  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 26-May 13

New Member - Working with xmlHTTP Object - Stuck...

Posted 26 May 2013 - 01:49 PM

Programming hobbyist here, have no schooling in programming so please excuse my ignorance.

I have been working with excel VBA (not sure if this goes under VB6 or VB.net). I have been trying to download stock data from MSN Money. My ultimate goal is to understand how to download webpages automatically. I can download the 10 year history and parse the data with VBA into excel. (http://investing.money.msn.com/investments/financial-statements?symbol=shw). However, when I try to get the data from the income statement, it is like the actual stock data is in a different URL inside an html frame called by javascript. ( http://investing.mon...ent/?symbol=shw )Accordingly, I am not sure how I grab this data. What's interesting is if I save the website from my chrome browser as a local html copy, everything I want is there, but when I try and use the xmlHTTP document object model to get the code, the actual html code is different. Maybe I am way in over my head here or this can't be done. I read on MSDN that there are some security features with frames and iframes so I am not sure if that is the problem.

So the question is, on the MSN Money income statement, how do I get the actual stock data using the xmlHTTP object in VBA?

If you can just give me some ideas, that may help me solve it. Do I need to set up another URL call based on the javascript on that page? If I can get the information from my chrome browser it sure seems like I should be able to get it with vba.

Also, as a side note, since I am new, I am not sure what programming language resources I should be using. A lot of the books I have looked at like Sam's Teach Yourself Visual basic don't seem very good. I really want to understand some concepts better. Like when I use vba, the object browser often fills in methods and properties of the object but sometimes it doesn't. Like, if I type in httpDoc.g (a list of properties and methods of the htmldocument object model will pop up) but then, other times, httpDoc.GetElementsByTagName("span"). lists nothing but I can type in item("1").innerHTML and get the text. Its like it does not know the methods and properties of the GetElementsByTagName("span") collection, and I don't understand why that is. It would be nice to get a reference that explains all that better. I feel like if I could understand a few concepts better I could do a job much faster. It seems sometimes things are objects and other times they are methods and properties of objects instead of objects within objects.

Any help appreciated.



    
Sub GetWebsite()
'THIS PROGRAM WORKS WITH MSN MONEY TO GET THE INCOME STATEMENT AND BALANCE SHEET FOR A STOCK
    Dim strStockPick As String
    Dim aVarMsgBoxReturn As Integer
    Dim HTMLDoc As New HTMLDocument
    Dim CurrentDoc
    Dim intInfoType1 As Integer
    
    CurrentDoc = ThisWorkbook.Name
    URL1 = "http://investing.money.msn.com/investments/financial-statements?symbol="
    URL2 = "http://investing.money.msn.com/investments/stock-income-statement/?symbol="
       
    'Enter Stock Letters into form
    UserForm1.Show
    strStockPick = UserForm1.TextBox1.Value
    aVarMsgBoxReturn = MsgBox("Your Stock Pick is " & strStockPick, vbOKOnly, "Stock To Analyze")
       
    intInfoType1 = 1 ' Get 10 year summary
    
    'Set up XML Response and get website text
    
    'XMLHttpSynchronous URL1 & strStockPick, intInfoType1 'Get 10 year summary first
    intInfoType1 = 2 ' Get financials
    URL2 = URL2 & Escape("us%3a") & strStockPick & Escape("&stmtView=Ann")
    XMLHttpSynchronous URL2 & strStockPick & "string", intInfoType1 'Get financials second
    
    Windows(CurrentDoc).Activate
    Sheets("Sheet1").Select
    
    End Sub
Function Escape(ByVal URL As String) As String
        'URLs cannot contain most special characters. _
         VBScript and Javascript have built-in Escape functions. _
         In VB we have to write our own
    Dim I As Integer, BadChars As String
    BadChars = "<>%=&!@#$^()+{[}]|\;:'"",/?"
    For I = 1 To Len(BadChars)
        URL = Replace(URL, Mid(BadChars, I, 1), "%" & Hex(Asc(Mid(BadChars, I, 1))))
        Next I
    URL = Replace(URL, " ", "+")
    Escape = URL
    End Function
Sub XMLHttpSynchronous(ByVal URL As String, ByVal intInfoType2 As Integer)

    Dim XMLHttpRequest As XMLHttp

    Set XMLHttpRequest = New MSXML2.XMLHttp
 
    XMLHttpRequest.Open "GET", URL, False
    XMLHttpRequest.send
     
    If XMLHttpRequest.Status = 200 And XMLHttpRequest.readyState = 4 Then
    Else
        strMsgFailedWebsite = MsgBox("The Website Failed to Load Properly", vbOKOnly, "Title")
        End
    End If
    
    Dim HTMLDoc As New HTMLDocument
    HTMLDoc.body.innerHTML = XMLHttpRequest.responseText
    WriteHTMLtoFile HTMLDoc
    If HTMLDoc.body.innerHTML = "" Then
    ElseIf intInfoType2 = 1 Then
        parseMSNMoney10yr HTMLDoc
    ElseIf intInfoType2 = 2 Then
        parseMSNMoneyIncomeStatement HTMLDoc
    End If
    
End Sub
Sub parseMSNMoney10yr(msnMoney10 As HTMLDocument)
    ' Select windows active sheet
        Dim CurrentDoc
        CurrentDoc = ThisWorkbook.Name
        Windows(CurrentDoc).Activate
        Sheets("Sheet1").Select
        Columns("A:A").Select
        
    'Call Parsing Subroutine, send htmldoc, the element spot, and in what column to place title
        parse10yearsummary msnMoney10, 1 'Parse the investment
        parseMSNMoneyFinancials msnMoney10, 2 'Parse Balance Sheet
        
    'Put everything in sheet 2 for trouble shooting
        Dim noOfElements As Integer
        Sheets("Sheet2").Select
        Columns("A:A").Select
        noOfElements = msnMoney10.getElementsByTagName("span").Length
        k = 1
        For k = 1 To noOfElements - 1
            Cells(k, 1) = msnMoney10.getElementsByTagName("span").Item(k).innerHTML
        Next
        

End Sub
Sub parse10yearsummary(ByVal parseMSNMoney10 As HTMLDocument, ByVal intInvType10 As Integer)

    'Declare Variables and Set Initial Values
        Dim m As Integer
        Dim ii As Integer
        Dim iii As Integer
        Dim i2 As Integer
        Dim i3 As Integer
        Dim k As Integer
        Dim intFirstRow As Integer
        Dim TitleColNo As Integer
        Dim intColsOfInfo As Integer
        Dim strElementContents As String
        Dim blnContainsMILorBil As String
        Dim dblCellValue As Double

        ii = 1
        iii = 1
        i2 = 1
        k = 3 'Begin data entry in row 3 (headers)
 
    'Parse Income Statement (1) or balance sheet (2)?
        If intInvType10 = 1 Then
            'FIND THE FIRST ROW FOR THE INCOME STATEMENT
                For i2 = 1 To parseMSNMoney10.getElementsByTagName("span").Length - 1
                    strElementContents = parseMSNMoney10.getElementsByTagName("span").Item(i2).innerHTML
                    If strElementContents Like "*INCOME STATEMENT*" Then
                        intFirstRow = i2
                    End If
                Next
                
                'MAKE SURE INCOME STATEMENT EXISTS OTHERWISE QUIT
                If parseMSNMoney10.getElementsByTagName("span").Item(intFirstRow).innerHTML Like "*INCOME STATEMENT*" Then
                Else
                    bs = MsgBox("There is no Income Statement Escape Programs", vbOKOnly)
                    Exit Sub
                End If
                
            TitleColNo = 1
            intColsOfInfo = 7
                      
        ElseIf intInvType10 = 2 Then
            'FIND THE FIRST ROW FOR THE BALANCE SHEET
                For i2 = 1 To parseMSNMoney10.getElementsByTagName("span").Length - 1
                    strElementContents = parseMSNMoney10.getElementsByTagName("span").Item(i2).innerHTML
                    If strElementContents Like "*BALANCE SHEET*" Then
                        intFirstRow = i2
                    End If
                Next
                
                'MAKE SURE BALANCE SHEET EXISTS OTHERWISE QUIT
                If parseMSNMoney10.getElementsByTagName("span").Item(intFirstRow).innerHTML Like "*BALANCE SHEET*" Then
                Else
                    bs = MsgBox("There is INCOME STATEMENT but no BALANCE SHEET Escape Programs", vbOKOnly)
                    Exit Sub
                End If
                
            TitleColNo = 10
            intColsOfInfo = 5
 
        End If
       
    'Parse the elements starting at the right span tag (i.e. n=33) and place into the sheet
        Cells(2, TitleColNo) = parseMSNMoney10.getElementsByTagName("span").Item(intFirstRow).innerHTML 'Set Title
        intFirstRow = intFirstRow + 1
        
        For ii = 1 To 11
        m = TitleColNo  ' M is the Column, K is the row
            For iii = 1 To intColsOfInfo
                If intFirstRow < 180 Then
                    strElementContents = parseMSNMoney10.getElementsByTagName("span").Item(intFirstRow).innerHTML
                    If k = 3 Then
                        strElementContents = Replace(strElementContents, "<BR>", " ") 'Remove HTML TAGS
                    End If
                    
                    ' MUST CONVERT THE NUMBERS FROM STRING TO NUMBER (REPLACE MIL AND BIL) FORMAT OTHER DATA TYPES AS ENTERED
                    If k > 3 Then
                        If strElementContents Like "*Mil*" Or strElementContents Like "*Bil*" Then
                            dblCellValue = ConvertMilAndBilToNumber(strElementContents)
                            Cells(k, m) = dblCellValue
                            ActiveSheet.Cells(k, m).Select
                            Selection.NumberFormat = "#,##0"
                            'ActiveCell.NumberFormat = "#,##0"
                            
                        ElseIf m = 1 Or m = 10 Then ' IS DATE and FORMAT DATE
                            'Check that string is data
                            If strElementContents Like "*##/##*" Then
                                strElementContents = parseTheDate10yr(strElementContents)
                                Cells(k, m) = strElementContents
                                ActiveSheet.Cells(k, m).Select
                                Selection.NumberFormat = "m/d/yyyy"
                                'ActiveCell.NumberFormat = "m/d/yyyy"
                            Else
                                    Exit Sub
                            End If
                                 
                        ElseIf m = 6 Or m = 7 Then
                            If strElementContents Like "*%*" Then
                                strElementContents = Replace(strElementContents, "%", "")
                            End If
                            Cells(k, m) = strElementContents
                            ActiveSheet.Cells(k, m).Select
                            Selection.NumberFormat = "0.00"
                            'ActiveCell.NumberFormat = "0.00"
                            
                        Else
                            Cells(k, m) = strElementContents
                        End If
                    Else
                       Cells(k, m) = strElementContents
                    End If
                    
                    intFirstRow = intFirstRow + 1
                    m = m + 1
                Else
                
                End If
            Next
        k = k + 1
        Next

End Sub
Sub parseMSNMoneyIncomeStatement(msnMoneyIncomeStatementHTML As HTMLDocument)
    ' Select windows active sheet
        Dim CurrentDoc
        CurrentDoc = ThisWorkbook.Name
        Windows(CurrentDoc).Activate
        Sheets("Sheet3").Select
        Columns("A:A").Select
        
    'Call Parsing Subroutine, send htmldoc, the element spot, and in what column to place title
        'parse10yearsummary msnMoney10, 1 'Parse the investment
        'parseMSNMoneyFinancials msnMoney10, 2 'Parse Balance Sheet
        
    'Put everything in sheet 2 for trouble shooting
        Dim noOfElements As Integer
        Sheets("Sheet3").Select
        Columns("A:A").Select
        noOfElements = msnMoneyIncomeStatementHTML.getElementsByTagName("span").Length
        k = 1
        For k = 1 To 1
            'Cells(k, 1) = msnMoneyIncomeStatementHTML.getElementById("FiscalPeriodEndDate").Item(k).innerHTML
            Cells(k, 1) = msnMoneyIncomeStatementHTML.body.innerHTML
        
            
        Next
End Sub

Function ConvertMilAndBilToNumber(ByVal strElementContents2 As String)
        Dim strMoneyArray() As String
        Dim dblMoneyValue As Double
        
        strMoneyArray() = Split(strElementContents2, " ")
        If strMoneyArray(1) Like "Mil" Then
            dblMoneyValue = CDbl(strMoneyArray(0))
            dblMoneyValue = dblMoneyValue * 1
            
        ElseIf strMoneyArray(1) Like "Bil" Then
            dblMoneyValue = CDbl(strMoneyArray(0))
            dblMoneyValue = dblMoneyValue * 1000
            
        End If
    
    ConvertMilAndBilToNumber = dblMoneyValue
    
End Function

Function parseTheDate10yr(strHtmlDate10yr As String) As String

    Dim strDateArray() As String
        
    strDateArray() = Split(strHtmlDate10yr, "/")
    If CInt(strDateArray(0)) <= 99 And CInt(strDateArray(0)) > 50 Then
        strDateArray(1) = "19" & strDateArray(1)
    Else
        strDateArray(1) = "20" & strDateArray(1)
    End If
    
    parseTheDate10yr = strDateArray(0) & "/1/" & strDateArray(1)

End Function
Sub WriteHTMLtoFile(ByVal HTMLDocToWrite As HTMLDocument)


Set fs2 = CreateObject("Scripting.FileSystemObject")
Set a2 = fs2.CreateTextFile("m:\testfileadf.txt", True)
a2.WriteLine (HTMLDocToWrite.body.innerHTML)
a2.Close


'Dim fso As New FileSystemObject
'Dim fso2 As New



' Declare a TextStream.
'Dim stream As TextStream
' Create a TextStream.
'Set stream = fso.CreateTextFile("C:\deletehtml.txt", True)
'stream.Write "This line uses the Write method."
'stream.Close
End Sub
  


Is This A Good Question/Topic? 0
  • +

Replies To: New Member - Working with xmlHTTP Object - Stuck...

#2 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10179
  • View blog
  • Posts: 37,584
  • Joined: 27-December 08

Re: New Member - Working with xmlHTTP Object - Stuck...

Posted 26 May 2013 - 01:52 PM

Welcome to DIC! Move to Other Languages. Please remember to post help questions in the help forums. :)
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,540
  • Joined: 12-December 12

Re: New Member - Working with xmlHTTP Object - Stuck...

Posted 26 May 2013 - 02:24 PM

You can get stock quotes directly in a worksheet. Go to the Data tab (Excel 2010), Existing Connections, MSNMoneyCentral .... So the information is placed in a worksheet, you can refresh this information, programmatically if necessary, and build formulas around it.

Autocompletion doesn't offer suggestions if VBA doesn't know exactly which object you are using. For example, Range works, but a Control or Sheet object won't.

I believe there are also add-ins available to obtain stock data, some of which are free.
Was This Post Helpful? 1
  • +
  • -

#4 beachy15  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 26-May 13

Re: New Member - Working with xmlHTTP Object - Stuck...

Posted 26 May 2013 - 06:09 PM

Thanks for the quick replies. Wow, I have never used that part of the "get external data" ribbon in excel 2010. What I have seen of the add-ins is that they give the stock quote only or very limited information. I would like to get all the detailed information about the stock, things like the number of employees, short term debt, long term debt, cash flow, etc... I have not seen that in add-ins. I would also like to grab all stocks (thousands of them). Of course I looked around for other software like Quantshare but nothing I seemed to find, fit. Other programs were like $500/month.

I still don't understand auto-completion. Are you saying it only works if it knows the specific instance of the class? For example it can't do autocomplete on the Sheet class without specifying a specific instance of that class, such as Sheet1 or ActiveSheet?
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,540
  • Joined: 12-December 12

Re: New Member - Working with xmlHTTP Object - Stuck...

Posted 26 May 2013 - 06:26 PM

View Postbeachy15, on 27 May 2013 - 01:09 AM, said:

I still don't understand auto-completion. Are you saying it only works if it knows the specific instance of the class? For example it can't do autocomplete on the Sheet class without specifying a specific instance of that class, such as Sheet1 or ActiveSheet?

It is not the specific instance so much, it is the type of your object. Range() is obvious to VBA and it displays all the Range members. If you have a reference to, for example, a form-control then it doesn't know what members to show; if, instead, it were a TextBox then this has specific members that it will list.

A Worksheet object is an exception to this rule, as Excel VBA doesn't show its members, even though its type is clear. It is not an exact science, particularly when you include other libraries.

[It does, however, work with a WorkSheet if you first create an object variable to reference it: Dim ws As Worksheet. ws. will then produce an auto-complete list.]

Sorry, but I can't help you further with your stock questions. Well.. if you are trying to read data inside an iframe, then you need a reference to this iframe and then to navigate within it using something like refToIframe.documentBody.otherElement(s).

This post has been edited by andrewsw: 26 May 2013 - 06:29 PM

Was This Post Helpful? 1
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,540
  • Joined: 12-December 12

Re: New Member - Working with xmlHTTP Object - Stuck...

Posted 26 May 2013 - 06:35 PM

It may require refToIframe.contentwindow.document.body.innerHTML (or other element(s)).

Edited: I don't think contentWindow is required, just .document.body to get inside the iframe.

This post has been edited by andrewsw: 26 May 2013 - 06:43 PM

Was This Post Helpful? 1
  • +
  • -

#7 beachy15  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 26-May 13

Re: New Member - Working with xmlHTTP Object - Stuck...

Posted 27 May 2013 - 06:04 AM

Thanks for your help. I wish their was guide that would explain that autocompletion better. Obviously, I understand better regarding the object type. I looked into getting external data with excel but that still seems limited as it relies on MSN money database.

I will see if I can figure out how to get that data using iframe reference and will try to post an explanation.
Was This Post Helpful? 0
  • +
  • -

#8 beachy15  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 26-May 13

Re: New Member - Working with xmlHTTP Object - Stuck...

Posted 30 May 2013 - 05:41 AM

Okay, to my problem above, i found a solution or workaround. I can download the webpage from the chrome browser and get the information I need, but when I use the XMLHTTP object, the data it gets is different, I believe it is using javascript to create the tables dynamically from a database. So what I did was use the URLDownloadToFile Function to download the html page (this renders the underlying html tables) and save the html page locally (i.e. C:/myfile.html). I then open the page and read it into an HTMLDocument object and then parse as usual. :)/>

Sub Sample()
    Dim strURL As String
    Dim strPath As String

    strURL = "http://investing.money.msn.com/investments/stock-income-statement/?symbol=shw"

    strPath = "M:\myfilename.html"

    Ret = URLDownloadToFile(0, strURL, strPath, 0, 0)

    If Ret = 0 Then
        MsgBox "File successfully downloaded"
    Else
        MsgBox "Unable to download the file"
    End If
End Sub

Sub LoadFileIntoHTMLDocObject()
    
    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Dim strPath2 As String
    Dim strFilePath As String
    Dim ReadFileIntoString As String
    Dim CurrentDoc
    CurrentDoc = ThisWorkbook.Name
    
    strFilePath = "M:\myfilename.html"
    
    Set ts = fso.OpenTextFile(strFilePath)
    ReadFileIntoString = ts.ReadAll
    strPath2 = "M:\myfileastext.txt"
    If fso.FileExists(strPath2) Then
    Else
        fso.CreateTextFile (strPath2)
    End If
    Set ts = fso.OpenTextFile(strPath2, ForAppending, True)
    ts.Write ReadFileIntoString
    ts.Close

    Dim htmlDoc2 As New HTMLDocument
    Dim anElement As String
    
    htmlDoc2.body.innerHTML = ReadFileIntoString
    
    'anElement = htmlDoc2.getElementsByTagName("div").Item(1).innerHTML
    anElement = htmlDoc2.getElementById("FiscalPeriodEndDate").innerHTML
    
    
    
    Windows(CurrentDoc).Activate
    Sheets("Sheet3").Select
    Cells(1, 1) = anElement
    
End Sub 

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1