iamaraymond

¦^´_ 6# DanielWONG

¦pªG¤£»Ý­n¤é´Áªº¸Ü¡A¥u­n§ï¤@¤UmyStrArr´N¦n
myStrArr = Array("FUND_NAME", "FUND_CURRENCY", "ASK_PRICE", "BID_PRICE")

¦Ü©ó¥u¨ú¨º¤TÀÉ°òª÷ªº¸ê®Æ
§Úı±o¤ñ¸û¦³¼u©Êªº§@ªk¬O¦b®ÇÃ䪽±µ¥Îvlookup¨ç¼Æ
«O«ù¸ê®Æªº§¹¾ã©Ê
¥H§K¤é«á­Y·Q­n¨ú¨ä¥L°òª÷ªº¸ê®Æ¤S­n§ï¤@¦¸µ{¦¡

f3202

¦^´_ 1# DanielWONG


    Sub ­»´ä¥æ©ö©Ò_test()
Dim Stock1 As String
Dim oXmlhttp As Object, oHtmldoc As Object, surl As String, E As Object, R As Integer, C As Integer
  Set oXmlhttp = CreateObject("msxml2.xmlhttp")
   Set oHtmldoc = CreateObject("htmlfile")
     surl = "https://hk.finance.yahoo.com/quote/0388.HK"
        With oXmlhttp
        .Open "Get", surl, False
        .send
        oHtmldoc.Write .responseText
    End With
    ActiveSheet.Cells.Clear
     With oHtmldoc
        Set E = .all.tags("table")(1)
        For R = 0 To E.Rows.Length - 1
            For C = 0 To E.Rows(R).Cells.Length - 1
                ActiveSheet.Cells(R + 1, C + 1) = E.Rows(R).Cells(C).innerText
            Next
        Next
    End With
    End Sub

DanielWONG

¦^´_ 3# iamaraymond


   
·Pı .ResponseText ¤º®e¦n¦h°Ú¡I
¨ä¹ê§Ú¤£»Ý­n¿ï¾Ü¤é´Á¡A¥u»Ý­n§ì·í«e¤é´Á Chinese Equity Fund¡A Global Bond Fund©MHang Seng Index Tracking Fund ¤TºØ°òª÷ªº·í«e»ù®æ¡C¬O§_¥i¯à§ì¤Ö¤@¨ÇªF¦èÅý.ResponseText¤º®e¤Ö¨Ç©O¡H

f3202

¦^´_ 3# iamaraymond

½Ð°Ý
https://djinfo.cathaysec.com.tw/z/ze/zef/zef.djhtm
©M   
http://justdata.yuanta.com.tw/z/ze/zeb/zeb.djhtm
³oºØ¤U¸ü«á¦³³¡¤ÀÁôÂäU¸ü¤£§¹¥þ
¸Ó«ç»ò¤U¸ü
ÁÂÁÂ

DanielWONG

¦^´_ 3# iamaraymond


    ¦hÁ¤j¯«¡A §Ú¥ý®ø¤Æ¤@¤U±zªº¤èªk¡I

iamaraymond

¦^´_ 1# DanielWONG

°õ¦æµ²ªGÀ³¸Ó¦p¤U¹Ï

¤U¸ü (128.14 KB)

2018-6-16 15:53

§Ú¬Ý¥LÁÙ¦³ÆZ¦h¸ê®Æ¥i¥H¥Îªº

¤U¸ü (100.93 KB)

2018-6-16 15:55

(¦ý§Ú¤£ª¾¹D¨º¨Ç¸ê®Æªº¥Î³~¡A©Ò¥H´N¨S¥[¤FXD)
¦pªG»Ý­nªº¸Ü¥i¦Û¦æ¥[¦bmyStrArr¸Ì

iamaraymond

¦^´_ 1# DanielWONG

¸Õ¸Õ¬Ý~

1. Sub test()
   
2. 
   
3. Cells.Clear
   
4. 
   
5. Dim myXML As Object
   
6. Set myXML = CreateObject("Winhttp.WinhttpRequest.5.1")
   
7. 
   
8. With myXML
   
9.     .Open "POST", "https://rbwm-api.hsbc.com.hk/pws-hk-hase-mpfunitprice-papi-prod-proxy/v1/mpf/getSuperTrustPlus", False
   
10.     .setRequestHeader "content-type", "application/json;charset=UTF-8"
    
11.     body = "{""locale"":""en""}"
    
12.     .send body
    
13.    
    
14.     myText1 = .responseText
    
15.     myText2 = Split(myText1, """trustPlusList"":[{""")(1)
    
16.     j = 1
    
17.     myStrArr = Array("FUND_NAME", "UNIT_PRICE_DATE", "FUND_CURRENCY", "ASK_PRICE", "BID_PRICE")
    
18.     For Each myStr In myStrArr
    
19.         myText3 = Split(myText2, myStr)
    
20.         For i = 1 To UBound(myText3)
    
21.             Cells(i + 1, j) = Replace(Replace(Split(myText3(i), ",")(0), ":", ""), Chr(34), "")
    
22.         Next
    
23.         j = j + 1
    
24.     Next
    
25.     Range("A1").Resize(1, UBound(myStrArr) + 1).Value = myStrArr
    
26.     'Debug.Print .responseText
    
27. End With
    
28. Set myXML = Nothing
    
29. End Sub

½Æ»s¥N½X

DanielWONG

:) ¦U¦ì¡A §Ú·Q¨C¤Ñ¥ÎVBA¦Û°Ê§ì¨ú¤U­±³sµ²ªººô¯¸

https://www.hangseng.com/en-hk/e-services/e-mpf/fund-price-performance/price/

¨C¤Ñ¦Û°Ê§ó·sªº°òª÷»ù®æ¸ê®Æ¡C
¤ñ¦p¡A
Name of Constituent Fund        Bid Price (HKD)        Offer Price (HKD)
Age 65 Plus Fund                        11.57                                   11.57
·Q§ì¨ú 11.57 ³o­Ó»ù®æ¡A  ½Ð°Ý¥Î¤°»ò¤èªk©O¡H

¸Õ¤Fquery table ©M ¸ÑªRHTML³£¤£¦¨¥\§r¡C ¦hÁ«üÂI°g¬z¡I:handshake

¤U¸ü (66.6 KB)
example

2018-6-16 14:56