CODEWITHSUNDEEP
jsonexcelpowerquery
Joshua Whitman
Joshua Whitman

Reputation: 11

How do I extract Json into an excel spreadsheet next to several parameters

I am trying to get financial data from Financial Modeling Prep's API into an excel spreadsheet. I am beginning to think that Power Query just does not do what I am looking for. I want to have one column with a static list of stock symbols (DAL, GOOG, AAL etc) and populate each row with financial data from various api calls such as the Net Income field from https://financialmodelingprep.com/api/v3/financials/income-statement/DAL and the current stock price from https://financialmodelingprep.com/api/v3/stock/real-time-price/DAL

Upvotes: 1

Views: 330

Answers (2)

JoseLuis
JoseLuis

Reputation: 1

Here is my solution in Python:

Create parameters:

company = "NVDA"
years = 5

Add Key:

api_key = 'YOUR_KEY'

Request:

r = requests.get(f'https://financialmodelingprep.com/api/v3/income-statement/{company}?limit={years}&apikey={api_key}')

data = r.json()

data

Extract data

date = []
symbol = []
revenue = []
costOfRevenue = []
grossProfit = []

for finance in data:
    date.append(finance["date"])
    symbol.append(finance["symbol"])
    revenue.append(finance["revenue"])
    costOfRevenue.append(finance["costOfRevenue"])
    grossProfit.append(finance["grossProfit"])

ncome_nvda_dict = {
    "Date" : date,
    "Ticket": symbol,
    "Revenue" : revenue,
    "CostOfRevenue" : costOfRevenue,
    "grossProfit" : grossProfit,

From Object To Pands

income_nvda_df = pd.DataFrame(income_nvda_dict, columns = ['Date', 'Ticket', 'Revenue', 'CostOfRevenue', 'grossProfit'])

Upvotes: 0

Ben
Ben

Reputation: 493

What exactly have you tried? It's very simple to extract data from the first link you gave with the M Code below (all UI based, nothing advanced about that at all). Converting that into a function to go to the relevant URL for each code and do the same transformation is also trivial

let
    Source = Json.Document(Web.Contents("https://financialmodelingprep.com/api/v3/financials/income-statement/DAL ")),
    financials = Source[financials],
    #"Converted to Table" = Table.FromList(financials, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"date", "Net Income"}, {"date", "Net Income"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Net Income", type number}, {"date", type date}})
in
    #"Changed Type"

Upvotes: 1

Related Questions