从晨星网络拉上升/下行捕获率 [英] Pulling Upside/downside Capture Ratio from morningstar.com

问题描述

第一次很长时间。

新的这个VBA的东西，但是捕捉。

New to this VBA thing, however catching on.

有兴趣为大量共同基金提取上行/下跌收购率，并希望自动化过程。
我正在采取的信息表不是你的典型表;我猜这是晨星网站上的动态对象
这是网站。

I'm interested in pulling the upside/downside capture ratio for a lot of mutual funds and want to automate the process. The table I am taking the info from is not your typical table; I guess it's a "dynamic object" on morningstar's website Here is the website.

http://performance.morningstar.com/fund/ratings-risk.action?t=FDSAX&地区=美国&文化= en-us

这是SunAmerica的焦点股利基金;然而，我想为许多基金做这个
这是我现在所用的代码;我收到了msgbox，但不知道如何循环并获取excel上的信息。

This is specically for SunAmerica's Focus Dividend Fund; however I want to do it for many funds Here is what I have for the code right now; I got it to msgbox, but don't know how to loop and get the information on excel.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column=Range("upDown").Row And _
  Target.Column= Range("upDown").Column Then

  Dim IE as New InternetExplorer
  IE.Visible=False
  IE.navigate "http://performance.morningstar.com/fund/ratings-risk.action?t=" _
     & Range("upDown").Value

  Do
    DoEvents
  Loop Until IE.readyState = READYSTATE_Complete
    Dim Doc as HTMLDocument
    Set Doc = IE.document
    Dim sTR As String 'got the "TR" from google chrome inspect element
    sTR = Trim(Doc.getElementsByTagName("tr")(45).innerText)

这是我被卡住的地方。我知道我需要使用'split'来排列我需要的每个数据。示例1年上升1年下降3年上涨3年下滑。

This is where I am stuck. I know that I need to used the 'split' in order to line item each of the data I need. Example 1year upside 1 year downside 3 year upside 3 year downside.

所以一旦我得到它在excel，我需要通过我的所有的代码...每个月更新一次约1500个数据。

So once I get it on excel, I need to have excel run through all of my tickers...about 1500 to pull that data since it updates once per month.

提前谢谢你，你会成为一个救命员...从字面上我可能会拍摄自己，如果我不明白：）

Thank you in advance...you'll be a life-saver...literally I might shoot myself if I don't figure it out :)

推荐答案

请尝试以下代码。

Sub Test()

    Dim IE As Object, Doc As Object, lastRow As Long, tblTR As Object, tblTD As Object, strCode As String
    lastRow = Range("A65000").End(xlUp).Row


    Set IE = CreateObject("internetexplorer.application")
    IE.Visible = True


    For i = 1 To lastRow

        strCode = "FDSAX"    ' Range("A" & i).value  ' kindly change it as per your requirement. Currently hardcoded

        IE.navigate "http://performance.morningstar.com/fund/ratings-risk.action?t=" & "FDSAX"

        Do While IE.readystate <> 4: DoEvents: Loop

        Set Doc = CreateObject("htmlfile")
        Set Doc = IE.document

tryAgain:
        Set tblTR = Doc.getelementbyid("div_upDownsidecapture").getelementsbytagname("tr")(3)

        If tblTR Is Nothing Then GoTo tryAgain

        j = 2
        For Each tblTD In tblTR.getelementsbytagname("td")
            tdVal = Split(tblTD.innerText, vbCrLf)
            Cells(i, j) = tdVal(0)
            Cells(i, j + 1) = tdVal(1)
            j = j + 2
        Next


    Next
End Sub

这篇关于从晨星网络拉上升/下行捕获率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！