How to let Power Query use openapi

MichaelV · ‎03-09-2023

Hi.

I use OpenAPI to connect to data from Saxobank trader platform.

I can use the openAPI as a formula builder but is very limited and I can use VBA to get data from the platform.

I login to the openapi (add-on to Excel) then I can write this in a cell:

=OpenApiGet("/openapi/ref/v1/instruments/details/?Uics="&Chart!B3&"&
AssetTypes="&Chart!B4&"&FieldGroups=","Symbol")

It would though be a big help if I could connect power query to the data platform as I then can see all tabels before I transform them.

I have this VBA code below --> is it possible to make a code in Power Query to draw data instead? I would prefer if you could make the code for me so I can connect through power query --> then I can see all columns and data and build from there.

VBA solution

To automate updates of the trade blotter, the VBA script below uses some familiar functionality to access data from the OpenAPI and loads it on the spreadsheet. In order to refresh the entire table, it is built from scratch every time the user hits the Update Blotter button. This means that all column headers, the construction of the table, and the sorting have to be repeated. The code below takes care of these tasks. Some key points here are:

As before, the input is verified to prevent unwanted errors. Because this is a relatively basic flow, the only check required concerns the result of the API call using the TypeName() function.
The table is created dynamically and always takes the full range of the loaded data.
On each refresh, the table is labeled 'Blotter' and sorted automatically descending by Time.

Sub GetHistoricTrades()

    Range("A6:J1008").ClearContents 'clear all data from sheet

    Dim ckey As String
    Dim query As String
    Dim fields As String
    Dim DataLog As Variant

    'set clientkey variable
    ckey = Application.Run("OpenApiGetClientKey")

    'set query and fields strings
    query = "/openapi/cs/v1/audit/orderactivities/?FieldGroups=" & _
    "displayandformat&Status=FinalFill&ClientKey=" & ckey
    fields = "ActivityTime,AccountId,BuySell,AssetType," & _
    "DisplayAndFormat.Description,DisplayAndFormat.Symbol,ExecutionPrice," & _
    "DisplayAndFormat.Currency,OrderType,Price"

    'pull entire activity log from OpenAPI
    DataLog = Application.Run("OpenApiGet", query, fields)
    
    If TypeName(DataLog) <> "Variant()" Then GoTo unexperror
    
    'set column names
    Range("A6").Value = "Time"
    Range("B6").Value = "Account"
    Range("C6").Value = "Action"
    Range("D6").Value = "Type"
    Range("E6").Value = "Description"
    Range("F6").Value = "Symbol"
    Range("G6").Value = "ExecutionPrice"
    Range("H6").Value = "Currency"
    Range("I6").Value = "Order"
    Range("J6").Value = "OrderPrice"
    
    'load all data onto sheet
    Range("A7:J" & 6 + UBound(DataLog)).Value = DataLog

    'create table
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$6:$J$" & 6 + UBound(DataLog)), , xlYes).Name = _
        "Blotter"
    Range("Blotter[#All]").Select
    ActiveWorkbook.Worksheets("Blotter").ListObjects("Blotter").Sort.SortFields. _
        Clear
    
    'sort by execution date
    ActiveWorkbook.Worksheets("Blotter").ListObjects("Blotter").Sort.SortFields. _
        Add2 Key:=Range("Blotter[[#All],[Time]]"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Blotter").ListObjects("Blotter").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    'update the pivot table on the stats sheet by refreshing its cash
    Sheets("Stats").PivotTables("Stats").PivotCache.Refresh

    Range("A7").Select
    Exit Sub

unexperror:     'capture unexpected error
    MsgBox "An unexpected error occured." & vbNewLine & _
    "Please check if you are logged in."
    Exit Sub

End Sub