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:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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:
Upvote 0
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
 
Upvote 0
Help please :(. Even though I can write basic VBA, but advance VBA like Kyle123's solution I dont quite understand.
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top