Get last price from WazirX exchange and automate trades

Without any json parser just with some VBA basics text function :

PHP Code: 

Sub DemoReq1()
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", "https://api.wazirx.com/uapi/v1/tickers/24hr", False
        .setRequestHeader "DNT", "1"
         On Error Resume Next
        .send
         If .Status = 200 Then V = Split(.responseText, "{""symbol"":""wrxusdt""")
    End With
         On Error GoTo 0
    If IsArray(V) Then
        If UBound(V) Then
            V = Split(Split(V(1), "}")(0), """lastPrice"":""")
            If UBound(V) Then MsgBox Val(V(1))
        End If
    End If
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Thanks for the rep' !

Originally Posted by sabha

I kept my symbol in range A1.

Try If .Status = 200 Then V = Split(.responseText, "{""symbol"":""" & [A1].Text & """")

As for some reason I can't use this API way so I can't help further …

Originally Posted by Marc L

As for some reason I can't use this API way so I can't help further …

Thank you for looking into my query. Its absolutely fine... I am trying to manage the trade endpoints somehow. Meanwhile, I have something to ask you.

In the above code we took only 1 symbol wrxusdt and placed it on A1. In case if I have 3 symbols (wrxusdt, maticusdt & bnbusdt) and place it on A1 B1 & C1 then do we have to make three different winhttp objects or can it be done within one object putting them in some loop? I tried to do something myself but my logic doesn't seem to be correct.

Also i am looking at putting these three symbols in an infinite FOR loop where new value is retrieved after every 5 seconds. I will write my condition in every loop to stop individual loops when the condition is met. For this example, can you please help me modifying the code as per my requirement and have a temporary condition that the first loop ends after 5 seconds, second in 10 seconds and third in 15 seconds.

Thanks

PS: I also wanted to understand what is "DNT" in this line .setRequestHeader "DNT"?

My previous VBA demonstration revamped :

PHP Code: 

Sub DemoReq1r()
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", "https://api.wazirx.com/uapi/v1/tickers/24hr", False
        .setRequestHeader "DNT", "1"
         On Error Resume Next
        .send
         If .Status = 200 Then T$ = .responseText
    End With
         On Error GoTo 0
    If T > "" Then
            V = [{"wrxusdt","maticusdt","bnbusdt"}]
        For C% = 1 To UBound(V)
                W = Split(T, "{""symbol"":""" & V(C) & """")
            If UBound(W) Then
                W = Split(Split(W(1), "}")(0), """lastPrice"":""")
                If UBound(W) Then V(C) = Val(W(1))
            End If
        Next
            [A1:C1].Value2 = V
    Else
        [A1:C1].ClearContents:  Beep
    End If
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

If you have enabled Option Explicit instruction you can remove it or you must declare each variable like Dim T$, C% for example …

As you can take a glance to the VBE Locals window during the execution in order to check each variable data type …

As my demonstration uses late binding so do not activate any reference or rewrite the code in order to not use any CreateObject statement …

Thanks for all your help !
It took me some time to completely understand the logic behind your code and how efficiently it was written. I also learnt about VBE Locals window which i never knew about. Thanks for that too...

I have one last query before I marked this thread as solved !

Referring to this line

Please Login or Register  to view this content.

I wish to keep it dynamic instead of hardcoded.

In my actual file, I have the three symbols in cells D8, F8 and H8. The code should be smart enough to generate a dynamic query string that will be passed to the V variant.
If only the D8 cell has a value then the query should be [{"wrxusdt"}] that goes into the V variable
If two of them are filled D8 & F8 then it should be [{"wrxusdt","maticusdt"}] else [{"wrxusdt","maticusdt","bnbusdt"}] if all cells (D8 F8 & H8) has a value
with a condition that if the first one D8 is blank and others are filled then it should remind me with a msgbox "First symbol is empty" and once OK is clicked, it should execute the rest of the code without exiting the for loop.
Similarly, if any other cell out of D8, F8, H8 is blank then the same condition should be checked.

I have tried different combination of double-quotes looking at the VBE Locals window but either it errors out or it includes quotes due to which the array V does not hold the proper value.

May I request your assistance on this please?

Thanks

As the initial question is solved so this is my last code here and
as important informations must be in the initial post in order to avoid a never ending story
as hidding important informations may lead to a less efficient procedure, somewhat a gas factory code …

PHP Code: 

Sub DemoReq1r2d2()
    Dim V, T$, C%, W, X()
        [A1:C1].ClearContents
        V = Filter(Application.Index([IF(D8:H8>"",D8:H8)], 1, [{1,3,5}]), False, False)
        If UBound(V) = -1 Then Beep: Exit Sub
        ReDim X(UBound(V))
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", "https://api.wazirx.com/uapi/v1/tickers/24hr", False
        .setRequestHeader "DNT", "1"
         On Error Resume Next
        .send
         If .Status = 200 Then T = .responseText
    End With
         On Error GoTo 0
    If T > "" Then
        For C = 0 To UBound(V)
                W = Split(T, "{""symbol"":""" & V(C) & """")
            If UBound(W) Then
                W = Split(Split(W(1), "}")(0), """lastPrice"":""")
                If UBound(W) Then X(C) = Val(W(1))
            End If
        Next
            [A1].Resize(, UBound(X) + 1).Value2 = X
    Else
        Beep
    End If
End Sub 


Originally Posted by Marc L

[INDENT]
Without any json parser just with some VBA basics text function :

Hello! could you please help me once again to convert this sub routine into a function? lets say A1 has the symbol name and in cell B1 if I write =DemoReq1(A1,lastprice), it should show me the last price of the symbol in A1. I tried myself but stuck at syntax on passing value to the fuction and fetching that value to add it in the array calculations. Thanks

No as within an UDF it may crash Excel …

Oh! I am assuming the reason would be a large response text to handle every time on every calculation activity in excel.

Do you think it is a better idea to scrape it within an UDF and return the last price and change percentage?

I am now trying to scrape these 2 values (change percentage & last price) from this web url (screenshot below) instead of api response text.
I tried it with some class names and XPATH given below but not getting to scrape the values. If you think it is possible to get it via UDF and if I can get some help here on this forum then I will start a new thread. Please advice. Thanks
class="sc-bwzfXH jsJuLQ"
class="sc-bwzfXH jaArUU"
class="price-box "
/html/body/div/div/div[2]/div[2]/div[1]/div/div[2]/a[6]/div[2]/div[2]/span/text()[2]
//*[@id="root"]/div/div[2]/div[2]/div[1]/div/div[2]/a[6]/div[2]/div[2]/span/text()[2]
WX.png

Yes it's better to open a new thread as this one is yet solved and
in order other helpers may show you other way like Excel web services, Power Query, …

[QUOTE=
{"code":2098,"message":"Request out of receiving window."}[/QUOTE]

Hi Sabha, could you please tell how did you solve this error?