How do I get balance sheet data from Alpha Vantage in JSON format?

Question

I tried the below code but it keeps returning a tuple. I know I can use the requests library but was willing to use the library. The library I am using is given here.

from config import API_KEY
from alpha_vantage.fundamentaldata import FundamentalData
from alpha_vantage.alphavantage import AlphaVantage

if __name__ == '__main__':
    fd = FundamentalData(key=API_KEY, output_format='json')
    bs = fd.get_balance_sheet_annual(symbol='IBM')
    print(bs)

The output is:

(                              fiscalDateEnding  ... commonStockSharesOutstanding
date                                            ...                             
1970-01-01 00:00:00.000000000       2022-12-31  ...                    906091977
1970-01-01 00:00:00.000000001       2021-12-31  ...                    898068600
1970-01-01 00:00:00.000000002       2020-12-31  ...                    892653424
1970-01-01 00:00:00.000000003       2019-12-31  ...                    887110455
1970-01-01 00:00:00.000000004       2018-12-31  ...                    892479411

[5 rows x 38 columns], 'IBM')

score 1 · Answer 1 · answered Aug 12 '23 at 10:57

Your bs is tuple of pandas dataframe and string i.e., name of symbol.it is very easy to convert pandas dataframe into JSON. so, first extract dataframe from tuple then apply to_json() method. Here T means transpose. The T method treat dataframe like matrix and transpose dataframe. it depends on you how do you want your JSON object. If you want it row wise, then use T ether don't use it.

json_data = bs[0].T.to_json()
print(json_data)

score 1 · Accepted Answer · answered Aug 12 '23 at 11:41

The issue is a check in the library that converts list responses to a Pandas dataframe, even if the output format is 'json' and not 'pandas'. From https://github.com/RomelTorres/alpha_vantage/blob/9d8e9fe44c88ec46019f4d99ae829ce502f1465e/alpha_vantage/alphavantage.py#L239:

if output_format == 'json':
    if isinstance(data, list):
        # If the call returns a list, then we will append them
        # in the resulting data frame. If in the future
        # alphavantage decides to do more with returning arrays
        # this might become buggy. For now will do the trick.
        if not data:
            data_pandas = pandas.DataFrame()
        else:
            data_array = []
            for val in data:
                data_array.append([v for _, v in val.items()])
            data_pandas = pandas.DataFrame(data_array, columns=[
                k for k, _ in data[0].items()])
        return data_pandas, meta_data
    else:
        return data, meta_data
elif output_format == 'pandas':
    if isinstance(data, list):
        # If the call returns a list, then we will append them
        # in the resulting data frame. If in the future
        # alphavantage decides to do more with returning arrays
        # this might become buggy. For now will do the trick.
        if not data:
            data_pandas = pandas.DataFrame()
        else:
            data_array = []
            for val in data:
                data_array.append([v for _, v in val.items()])
            data_pandas = pandas.DataFrame(data_array, columns=[
                k for k, _ in data[0].items()])

This looks like a bug, given that the json format is not supposed to output Pandas and the code is repeated directly below for the actual Pandas output format.

You can patch out this code in the library so the else clause is always used, for example

if output_format == 'json':
    if isinstance(data, list) and False:
    # [...]
    else:
        return data, meta_data

Then the code in the MCVE from the question returns the following:

([{'fiscalDateEnding': '2022-12-31', 'reportedCurrency': 'USD', 'totalAssets': '127243000000', 'totalCurrentAssets': '29118000000', 'cashAndCashEquivalentsAtCarryingValue': '7886000000', 'cashAndShortTermInvestments': '7886000000', 'inventory': '1552000000', 'currentNetReceivables': '14209000000', 'totalNonCurrentAssets': '96874000000', 'propertyPlantEquipment': '5334000000', 'accumulatedDepreciationAmortizationPPE': '13361000000', 'intangibleAssets': '67133000000', 'intangibleAssetsExcludingGoodwill': '11184000000', 'goodwill': '55949000000', 'investments': 'None', 'longTermInvestments': '142000000', 'shortTermInvestments': '852000000', 'otherCurrentAssets': '2610000000', 'otherNonCurrentAssets': 'None', 'totalLiabilities': '105222000000', 'totalCurrentLiabilities': '31505000000', 'currentAccountsPayable': '4051000000', 'deferredRevenue': '15531000000', 'currentDebt': '9511000000', 'shortTermDebt': '4760000000', 'totalNonCurrentLiabilities': '83414000000', 'capitalLeaseObligations': '164000000', 'longTermDebt': '47190000000', 'currentLongTermDebt': '4676000000', 'longTermDebtNoncurrent': '46189000000', 'shortLongTermDebtTotal': '107759000000', 'otherCurrentLiabilities': '9788000000', 'otherNonCurrentLiabilities': '12243000000', 'totalShareholderEquity': '21944000000', 'treasuryStock': '169484000000', 'retainedEarnings': '149825000000', 'commonStock': '58343000000', 'commonStockSharesOutstanding': '906091977'}, {'fiscalDateEnding': '2021-12-31', 'reportedCurrency': 'USD', 'totalAssets': '132001000000', 'totalCurrentAssets': '29539000000', 'cashAndCashEquivalentsAtCarryingValue': '6650000000', 'cashAndShortTermInvestments': '6650000000', 'inventory': '1649000000', 'currentNetReceivables': '14977000000', 'totalNonCurrentAssets': '101786000000', 'propertyPlantEquipment': '5694000000', 'accumulatedDepreciationAmortizationPPE': '14390000000', 'intangibleAssets': '68154000000', 'intangibleAssetsExcludingGoodwill': '12511000000', 'goodwill': '55643000000', 'investments': '199000000', 'longTermInvestments': '159000000', 'shortTermInvestments': '600000000', 'otherCurrentAssets': '5663000000', 'otherNonCurrentAssets': 'None', 'totalLiabilities': '113005000000', 'totalCurrentLiabilities': '33619000000', 'currentAccountsPayable': '3955000000', 'deferredRevenue': '16095000000', 'currentDebt': '13551000000', 'shortTermDebt': '6787000000', 'totalNonCurrentLiabilities': '90188000000', 'capitalLeaseObligations': '63000000', 'longTermDebt': '56193000000', 'currentLongTermDebt': '6728000000', 'longTermDebtNoncurrent': '44917000000', 'shortLongTermDebtTotal': '110496000000', 'otherCurrentLiabilities': '9386000000', 'otherNonCurrentLiabilities': '13996000000', 'totalShareholderEquity': '18901000000', 'treasuryStock': '169392000000', 'retainedEarnings': '154209000000', 'commonStock': '57319000000', 'commonStockSharesOutstanding': '898068600'},
# lots more json data
], 'IBM')

How do I get balance sheet data from Alpha Vantage in JSON format?

2 Answers2