Kraken OHLC data, can be somehow added possibility to to it for all Crypto

[Mino-sk]

Its possible to modify code in Kraken OHLC data, where C8=(cellInputInterval)=10080 (minutes) =>it returns two lines for selected AssetPair in C6 and time C10=(NOW()-14 days)=cellInputSince
but for every AssetPair in one table? (list of AssetPairs, can be stored in same Sheet(Kraken OHLC data) for examle in O1:O120 (just EUR - AssetPairs) - already there

Result table can looks like:

assetpair	time	open	high	low	close	vwap	volume	count
1INCHEUR	1632355200​	2,242​	3,413​	1,9​	2,151​	2,302​	284549,573​	1410​
1INCHEUR	1632960000​	2,145​	2,819​	2,145​	2,781​	2,486​	274861,451​	1120​
AAVEEUR	1632355200​	258,56​	265​	216,22​	228,35​	238,2​	15688,1495​	6023​
AAVEEUR	1632960000​	228,47​	278,31​	227,57​	262,76​	257,89​	7139,28696​	3555​
ADAEUR	1632355200​	1,933152​	2,099​	1,733001​	1,779817​	1,888022​	55950292,8​	67167​
ADAEUR	1632960000​	1,778962​	1,999294​	1,769856​	1,912947​	1,898818​	27237814,8​	36205​

[Querela]

Hi, yes, this is possible. I will take a look on the weekend. It basically boils down to requesting OHLC for two timestamps and outputting them interleaved.
I need to say that my sheets were more of an example of how to use the VBA modules, so they were rather basic. 🥲

https://github.com/Querela/Kraken-API-VBA/blob/master/example/Tabelle6.cls (OHLC)

• Kraken-API-VBA/example/Tabelle6.cls

  Line 82 in d92cb79

  curOffset = offsetTopResults + i

  you would need to have i * 2 (so output the first OHLC result every second row),
• and then the bigger hassle of finding the matching timestamps (14 day difference), (but probably just by looking at the first/last OHLC timestamp result?),
• doing a second request like

  Kraken-API-VBA/example/Tabelle6.cls

  Line 54 in d92cb79

  Set result = API.KrakenQueryPublic(method, params)

  but with updated params("since") value,
• and then output the older results like

  Kraken-API-VBA/example/Tabelle6.cls

  Line 82 in d92cb79

  curOffset = offsetTopResults + i

  but with + 1 to the offset and again with i * 2 instead of i

[Mino-sk]

Hello Erik, my point of interest is MAX/MIN of value in selected time period 14 days, for every AssetPair, when I use 10080 (minutes) it´s return two rows and in fileds High and Low it show lowest and highest value in 14 days. I am using a formula for TimeStamp: "N4"=FLOOR(NOW();1/24)-14 and convert it into unix time format: =(N4-DATE(1970;1;1))*86400 Kind Regads Milan

…

________________________________ From: Erik Körner ***@***.***> Sent: Friday, October 15, 2021 6:04 PM To: Querela/Kraken-API-VBA ***@***.***> Cc: Mino-sk ***@***.***>; Author ***@***.***> Subject: Re: [Querela/Kraken-API-VBA] Kraken OHLC data, can be somehow added possibility to to it for all Crypto (#1) Hi, yes, this is possible. I will take a look on the weekend. It basically boils down to requesting OHLC for two timestamps and outputting them interleaved. I need to say that my sheets were more of an example of how to use the VBS modules, so they were rather basic. 🥲 https://github.com/Querela/Kraken-API-VBA/blob/master/example/Tabelle6.cls (OHLC) * https://github.com/Querela/Kraken-API-VBA/blob/d92cb79e07d88298f02a6b6a604342a0c9b8826c/example/Tabelle6.cls#L82 you would need to have i * 2 (so output the first OHLC result every second row), * and then the bigger hassle of finding the matching timestamps (14 day difference), (but probably just by looking at the first/last OHLC timestamp result?), * doing a second request like https://github.com/Querela/Kraken-API-VBA/blob/d92cb79e07d88298f02a6b6a604342a0c9b8826c/example/Tabelle6.cls#L54 but with updated params("since") value, * and then output the older results like https://github.com/Querela/Kraken-API-VBA/blob/d92cb79e07d88298f02a6b6a604342a0c9b8826c/example/Tabelle6.cls#L82 but with + 1 to the offset and again with i * 2 instead of i — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#1 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AWBZC24I47HSQS76S2BFYRLUHBGHHANCNFSM5F7AKTWA>. Triage notifications on the go with GitHub Mobile for iOS<https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android<https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[Mino-sk]

I do some changes, but now it works, like I expect. list of "AllCrypto" I am reading from cells O16:O:216
Them for each CryptoPair from list, with condition of suitable interval(10080) and last 14/30... days load required data into original table.

CODE:
Option Explicit

' #############################################################################

Private Const cellInputAssetPair As String = "C6"
Private Const cellInputInterval As String = "C8"
Private Const cellInputSince As String = "C10"
Private Const cellLinkButton As String = "B12"
Private Const cellOutputErrMsg As String = "D12"
Private Const offsetTopResults As Integer = 16
Private Const rangeResultsToClear As String = "B16:J800" 'J735

' #############################################################################
' Utils

' #############################################################################

Private Sub DoGetOHLCData()
Dim method As String
Dim iii As Integer
Dim params As Object
Dim result As Variant, errJson As Variant
Dim assetpair As String, sInterval As String, sSince As String
Dim i As Integer, curOffset As Integer, arrKeys() As Variant, key As String, data As Variant, item As Variant
Dim transfer As Integer

' TODO: Q'n'D clearing ...
ActiveSheet.Range(rangeResultsToClear).ClearContents

'Application.Wait (Now + TimeValue("0:00:02"))

For iii = 16 To 200

    Set params = CreateObject("Scripting.Dictionary")

    ' prepare params
    method = "OHLC"

    ' Check if assetpair given
    assetpair = ActiveSheet.Cells(iii, 15).Value
    If Trim(assetpair & vbNullString) = vbNullString Then Exit Sub

    If VBA.Len(assetpair) > 2 Then
        params.Add "pair", assetpair
    End If
    sInterval = ActiveSheet.Range(cellInputInterval).Value
    If VBA.Len(sInterval) > 0 Then
        params.Add "interval", sInterval
    End If
    sSince = ActiveSheet.Range(cellInputSince).Value
    If VBA.Len(sSince) > 0 Then
        params.Add "since", sSince
    End If

    ' query public API, receive JSON structure
    Set result = API.KrakenQueryPublic(method, params)
    Debug.Print method, WebUtils.BeautifyJson(result)

    errJson = result("error")
    If Not IsEmpty(errJson) Then
        ActiveSheet.Range(cellOutputErrMsg).Value = errJson(0)
        Exit Sub
    Else
        ActiveSheet.Range(cellOutputErrMsg).ClearContents
    End If


    Set result = result("result")
    arrKeys = result.Keys

    ' update since for new query (nastaví poslednú dátumovú hodnotu do bunky C10, pre ponechanie odkomentovat spodný riadok)
    ' ActiveSheet.Range(cellInputSince).Value = result("last")

    ' append (?) data
    key = arrKeys(0)
    data = result(key)

    For i = 0 To UBound(data)
        If i Mod 25 = 0 Then VBA.DoEvents

        item = data(i)
        curOffset = offsetTopResults + transfer + i
        ActiveSheet.Range("B" & curOffset).Value = key
        ActiveSheet.Range("C" & curOffset).Value = item(0)
        ActiveSheet.Range("D" & curOffset).Value = item(1)
        ActiveSheet.Range("E" & curOffset).Value = item(2)
        ActiveSheet.Range("F" & curOffset).Value = item(3)
        ActiveSheet.Range("G" & curOffset).Value = item(4)
        ActiveSheet.Range("H" & curOffset).Value = item(5)
        ActiveSheet.Range("I" & curOffset).Value = item(6)
        ActiveSheet.Range("J" & curOffset).Value = item(7)
        
        
    Next
    transfer = curOffset - offsetTopResults + 1
    

Next iii

End Sub

' #############################################################################
' 'Official' event handlers

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim cellName As String

cellName = ExcelUtils.ColumnName(Target.Range.Column) & Target.Range.Row

If cellLinkButton = cellName Then
    Debug.Print "Get OHLC Data ..."
    'Application.ScreenUpdating = False
    On Error Resume Next
    DoGetOHLCData
    'Application.ScreenUpdating = True
End If

End Sub

' #############################################################################