VBA to extract Java script table

Acholate

New Member
Joined
Aug 6, 2013
Messages
26
Hi

I need help on VBA which able to extract table from this site

https://www.thaidw.com/tools/livematrix/AOT28C1804A?lang=en

The table that contain underlying bid & DW bid

note: "AOT28C1804A" part is DW symbol which will vary

Im not sure whether it is possible...I tried to look at source code but my knowledge on java/html is very limited.
I know how to write VBA to extract table using web query but it didnt work in this case.
If someone could provide me the code I would be very grateful.

Thanks in advance and sorry for my english
 
Last edited:

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,667
You'll need to add a module called JSONConverter and add the code from here: https://raw.githubusercontent.com/VBA-tools/VBA-JSON/master/JsonConverter.bas
You'll need to add a reference to Microsoft Scripting Runtime

You can then add the below to a module, call the getData sub with the DW symbol that's required.

That data will be placed into the first sheet, starting in cell a1
Rich (BB code):
Option Explicit


Private Type Midpoint
    price As Double
    diff As Double
    index As Long
End Type

Sub getData(code As String)
    
    Const length As Long = 9
    
    Dim start       As Long
    Dim x           As Long
    Dim data        As Object
    Dim livematrix  As Object
    Dim line        As Variant
    Dim mp          As Midpoint
    Dim output()    As Double
    
    'AOT28C804A
    
    Set data = getDataFromCode(code)
    Set livematrix = data("livematrix")

    mp = findMidpoint(livematrix, Val(data("ric_data")("lmuprice")))
    
    start = mp.index
    
    If start = 0 Then
        start = Int((livematrix.Count / 2) + 1)
    End If
    
    ReDim output(1 To ((start + length) - (start - length) - 1), 1 To 2)
    
    For Each line In livematrix
        If x > start - length And x < start + length Then
            output(x - (start - length), 1) = livematrix(x + 1)("underlying_bid")
            output(x - (start - length), 2) = livematrix(x + 1)("bid")
        End If
        x = x + 1
    Next line
   
    Sheets(1).Range("a1").Resize(UBound(output), 2).Value2 = output
   

End Sub



Public Function getDataFromCode(code As String) As Object
    
    Const url As String = "https://www.thaidw.com/LiveMatrixJSON?ric={{code}}.BK&mode=0"
    
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", Replace(url, "{{code}}", code), False
        .send
        Set getDataFromCode = JSONConverter.ParseJson(.responsetext)
    End With

End Function




Public Function findMidpoint(lmdata, lmPrice As Double) As Midpoint

    Dim mp      As Midpoint
    Dim x       As Long
    Dim line    As Variant
    Dim diff    As Double
    Dim ubid    As Double
    
    mp.diff = -1
    mp.price = -1
    mp.index = -1
    
    For Each line In lmdata
    
        ubid = lmdata(x + 1)("underlying_bid")
        diff = lmPrice - ubid
        
        If (mp.index = -1 Or mp.diff > diff) Then
            mp.diff = diff
            mp.price = ubid
            mp.index = x
        End If
        
        x = x + 1
    Next line
    
    findMidpoint = mp

    
End Function
 
Last edited:

Acholate

New Member
Joined
Aug 6, 2013
Messages
26
Thanks so much for solution
However, Im a bit confuse. I suppose to call the getData sub right (Call getData)? And how to input DW symbol?
What I did with others DW symbol (from other site) is input it in sheet called PriceMap cell B2-B13.
Something like this.

Input Stock nameDW List
AOTAOT01C1710A
AOT01C1801A
AOT01P1710A
AOT01P1801A
AOT13C1801A
AOT13C1803A
AOT13P1802A
AOT28C1711A
AOT28C1802A
AOT28P1802A

<tbody>
</tbody>

I run VBA to loop each DW symbol and put it in the same sheet
 

Acholate

New Member
Joined
Aug 6, 2013
Messages
26
Still need help. I dont really understand how to input DW symbol using Kyle123 solution.
 

Acholate

New Member
Joined
Aug 6, 2013
Messages
26
Help please :(. Even though I can write basic VBA, but advance VBA like Kyle123's solution I dont quite understand.
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,667
Where exactly have you got stuck? The process you describe is exactly right.

I don't really know how I can make it any more simple, what have you tried?
 

Acholate

New Member
Joined
Aug 6, 2013
Messages
26
You said "call the getData sub with the DW symbol that's required". I don't know where should I input DW symbol in your code. I just see you put 'AOT28C1804A as comment.
I also tried to replace this line
Const url AsString = "https://www.thaidw.com/LiveMatrixJSON?ric={{code}}.BK&mode=0" to "https://www.thaidw.com/tools/livematrix/AOT28C1804A?lang=en" but it didn't work.
When I try to Call getData there is compile error: argument not optional.



 
Last edited:

Acholate

New Member
Joined
Aug 6, 2013
Messages
26
I finally make it work!. Just knew that i have to put DW symbol in bracket...
quite stupid from me taking so long to realise this.
 

Forum statistics

Threads
1,078,346
Messages
5,339,677
Members
399,318
Latest member
kryten68

Some videos you may like

This Week's Hot Topics

Top