How to organize data from API source to output from Python into Excel?

I’m using requests (see below) to call API source and fetch some data (see below). I’m not sure how to deal with it.

It looks like JSON and/or a dictionary but I have no idea how to process it in Python before exporting to Excel. I understand, I have not imported JSON at this stage but I can.

My code:

url = 'https://quoteapi.com/api/v5/symbols/hvn.asx?appID=af5f4d73c1a54a33&averages=1&fundamentals=1&liveness=delayed'
result = requests.get(url, headers=headers)

Data returned:

{
  "symbol": "hvn.asx",
  "realSymbol": "hvn.asx",
  "units": "price",
  "currency": "AUD",
  "tz": "AEST",
  "tickTable": "0.0005<0.1,0.0025<2,0.005",
  "quote": {
    "pctChange": 0.556,
    "quoteBases": {
      
    },
    "low": 5.39,
    "open": 5.4,
    "vwap": 5.430336243,
    "value": 16256099.035,
    "time": "2021-07-07 16:49:15",
    "change": 0.03,
    "price": 5.43,
    "status": "ADJUST_ON",
    "close": 5.43,
    "bid": 5.43,
    "prevClose": 5.4,
    "lastTradeTime": "2021-07-07 16:49:15",
    "ask": 5.44,
    "volume": 2993572,
    "high": 5.48,
    "tradeCount": 5260
  },
  "sequence": 6502147,
  "desc": {
    "firstActive": "1990-08-06",
    "underlyingIssuerSymbol": "hvn.asx",
    "issuerName": "Harvey Norman Holdings Ltd",
    "isin": "AU000000HVN7",
    "securityType": "01",
    "shortDesc": "FPO",
    "lastActive": "2021-07-07",
    "abbrevDesc": "ORDINARY",
    "longDesc": "ORDINARY FULLY PAID",
    "shortName": "HARVEY",
    "underlyingIssuerNdividendPerShare": 0.38,
    "earningsPerShare": 0.5857
  },
  "averages": {
    "ytd": {
      "vwap": 5.395,
      "tradeCount": 4418,
      "high": 6.09,
      "value": 18583818.22,
      "volume": 3431631,
      "low": 4.7,
      "open": 4.69
    },
    "year": {
      "vwap": 4.884,
      "tradeCount": 4240,
      "high": 6.09,
      "value": 18055781.32,
      "volume": 3734356,
      "low": 3.49,
      "open": 3.59
    },
    "month": {
      "vwap": 5.293,
      "tradeCount": 4322,
      "high": 5.69,
      "value": 20414631.17,
      "volume": 3865196,
      "low": 5.07,
      "open": 5.36
    },
    "week": {
      "vwap": 5.508,
      "tradeCount": 3979,
      "high": 5.69,
      "value": 19188403.29,
      "volume": 3486067,
      "low": 5.37,
      "open": 5.4
    }
  }
}

Answer

Continuing from your code, you can parse the json string directly with a requests method:

obj = result.json()

You now have a python object called obj, which is a complex structure of nested dictionaries. That’s very easy to manipulate in python, but it certainly doesn’t look tabular, so it doesn’t make much sense to try to export that to Excel. Also, as noted in the comments, you haven’t indicated what the result should look like.

However, the averages key inside your data does seem tabular, here’s how you can create a dataframe from it and export it to excel:

import json

with open("foobar.json", "r") as f:
    obj = json.load(f)

with open("averages.json", "w") as f:
    json.dump(obj['averages'], f)

df = pd.read_json('averages.json').T
df.to_excel('averages.xlsx')

Note the obj['averages'] in the json.dump call. Also, I used the T function to transpose the dataframe, it seemed more natural (but that’s optional. Here’s what the dataframe looks like:

In [5]: df
Out[5]: 
        vwap  tradeCount  high        value     volume   low  open
ytd    5.395      4418.0  6.09  18583818.22  3431631.0  4.70  4.69
year   4.884      4240.0  6.09  18055781.32  3734356.0  3.49  3.59
month  5.293      4322.0  5.69  20414631.17  3865196.0  5.07  5.36
week   5.508      3979.0  5.69  19188403.29  3486067.0  5.37  5.40