question

Upvotes
Accepted
vijay.singh avatar image
43 ●2 ●1 ●7

parse RKD fundamental api data from json to pandas dataframe

Hello Team, I am facing problem in parsing RKD fundamental api data from json to pandas dataframe.

Code:

appid = ""

token = ""


# input_1 : appid and token

# RKD api request message headers

message_headers = {

'content-type': 'application/json;charset=utf-8' ,

'X-Trkd-Auth-ApplicationID': appid,

'X-Trkd-Auth-Token' : token

}


# input_2 : api url

# RKD FinancialStatements Service URL

api_url = 'http://api.trkd.thomsonreuters.com/api/Fundamentals/Fundamentals.svc/REST/Fundamentals_1/GetFinancialStatementsReports_1'


# input 3 : message_request(api dependent)

company_id = 'IBM.N'

message_request = {

'GetFinancialStatementsReports_Request_1':{

'companyId': company_id,

'companyIdType': 'RIC'

}

}


# get api response

api_response = doSendRequest(api_url, message_request, message_headers)

print(api_response)



I have attached the api json response in the text file.

GetFinancialStatementsReports_Request_1_response.txt


PFB the screenshot of expected data format(similar format is required in pandas dataframe).


rkd-apirkdfundamentalspandas
getfinancialstatementsreports-request-1-response.txt (183.0 KiB)
fund-data.jpg (156.7 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

2 Answers

Upvotes
Accepted
Gurpreet avatar image
13k ●32 ●12 ●18

Hi @vijay.singh,

Its best to seek help related to pandas on their docs site or on stackexchange. I tried this and was able to get some matching content. Other items like renaming index series and appending data will require iterative python loops.

jData = json.loads(data_string)
df = pd.DataFrame()
for fYear in jData['GetFinancialStatementsReports_Response_1']['FundamentalReports']['ReportFinancialStatements']['FinancialStatements']['AnnualPeriods']['FiscalPeriod']:
    df1 = pd.DataFrame(fYear['Statement'][0]['lineItem']).set_index('coaCode').rename(columns={'Value': fYear['FiscalYear']})
    if not df.size:
        df = df.append(df1)
    else:
        df = df.join(df1)




1604344331284.png (42.2 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Upvotes
vijay.singh avatar image
43 ●2 ●1 ●7

@Gurpreet, thanks a lot for above example. I will surely help me to proceed further.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Click below to post an Idea Post Idea