Excel and API Connections

Working with Power Query M to parse JSON responses from Web API

I did something cool with Excel today so, I’ve decided to share it here.

Today, my wife came to me and asked how we can see all the out crypto trades in one picture and based on that we can understand how much was my benefit from the beginning. Recently, she has started to trade on some crypto currencies which was available on an online platform. The platform she is using is limited for a few crypto currencies. The problem is after everyday transactions she loses where she was. She asked me for a solution.

First of all, like all other developers/programmers in the world, I have started to think about an application. I thought if I get the prices via an Api, then I can create an application to calculate and show the required information. Hopefully, I could found an Api from that platform quickly. However, I realized, I don’t have enough time to spend on the application development. But there should be an easier way to do this right? It’s not like 10 years ago with limited tools and available technologies to work and use.

Then, 💡, I asked myself what if I can do this by Excel and I did it. The final result was very lovely. An Excel file which is getting data from an Api every 10 minutes and updates the portfolio calculation result.

Let me explain how I did this without getting any help from google (as well as bing :).

Here are the steps to do this:

• Navigate to the Data tab and click on From Web button
• Give Excel your Api url

The Api I have, give me a JSON result in the following structure for ETHMYR

{
  "pair": "ETHMYR",
  "duration": 86400,
  "candles": [
    {
      "timestamp": 1609891200,
      "open": "4487.730096",
      "close": "4872.31781",
      "high": "5129.696726",
      "low": "4240.89",
      "volume": "0.00"
    },
...

As you can see, I need to read the value of close in the last item of the candles array.

response.candles[response.candles.length — 1].close

Here is the result:

Immediately, Excel asked me to convert the result into table. So, I clicked on that.

I need candles data. So, I click on it and ask to convert it to list.

It gives me all the items of the candles array. Obviously, I need the last item that is the latest price at the moment. I Click on Keep Items and select Keep Bottom Items.

It ask me how many items to keep. Enter 1 and continue.

Now, need to convert the result into table again. Click on To Table button.

Looks like we are in a place that we can finalize our response processing.

Need to convert the last step result to a list and again to a table.

Very well, the data is almost ready. Now, better to convert timestamp value into real date and time.

Click on the value and choose Replace Value from Transform Tab

Excel asking me to replace the value with another value. Because both are the real values and it can be change with any request, click on OK for now to get the query.

This should be something like the following query:

= Table.ReplaceValue(
     #"Converted to Table2",
     1612483200,
     "",
     Replacer.ReplaceValue,{"Value"})

And I need to replace it with the following query:

= Table.ReplaceValue(
   #"Converted to Table2",
   #"Converted to Table2"{0}[Value],
   DateTime.ToText(DateTime.LocalNow(),
   Replacer.ReplaceValue,{"Value"}))

And there it is

You can configure an interval to refresh this Query and insert the result into cells to use the values of it inside your Excel sheet.

The final query for me is like below which you can find and edit it by click on Advanced Editor.

let
    Source =  Json.Document(Web.Contents("https://[myapiurl]")),
        #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{2}[Value],
    #"Kept Last Items" = List.LastN(Value, 1),
    #"Converted to Table1" = Table.FromList(#"Kept Last Items",   
         Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Column1 = #"Converted to Table1"{0}[Column1],
    #"Converted to Table2" = Record.ToTable(Column1),
    #"Replaced Value" = Table.ReplaceValue(
        #"Converted to Table2",
        #"Converted to Table2"{0}[Value],
        DateTime.ToText(DateTime.LocalNow()),
        Replacer.ReplaceValue,{"Value"})
in
    #"Replaced Value"

And the lesson is, think simple and implement your idea as fast as you can. Let’s your brain to move forward fast.