Financial API JSON to Excel: Flatten It Without Python
Quote, statement and price APIs return nested JSON. Here is how to get it into a clean table an analyst can open in Excel.
Open the API Response to Table
Financial APIs rarely return a flat list. A typical response wraps the records in a status object, then a data list, and inside each record there are nested fields such as a currency block, a list of prices or a metadata object. JSON allows this structure. A spreadsheet has no place for it, which is why pasting the response into Excel gives one unreadable cell.
The usual fix is a script: call the API with requests, load the JSON with pandas and flatten it with json_normalize. That works, but it is a poor fit for an analyst who needs the data once, or for a reviewer who needs to check what was done. The script also has to be kept, shared and run.
API Response to Table does the same job in the browser. Enter the API address, choose Bearer token, API key in a header or Username and password, and send a GET request. The JSON becomes a table with one row per record. Nested fields get their full path as the column name, for example quote.currency or meta.exchange, so nothing is lost. If the response holds several lists, pick the one you want. Then download CSV, Excel, JSON or Parquet.
Check the result before you rely on it. Look at the first rows, confirm that dates and numbers look right, and keep identifiers such as ticker symbols or account codes as text so leading zeros are not dropped. Use the Data Profiler to count blanks and duplicates before the file goes into a report.
A note on privacy. The request goes straight from your browser to the API and Data Utility does not receive the response or your token. Use a read-only token where the provider offers one. Only GET requests over https are supported, and an API that does not allow browser calls (CORS) will be blocked. This is about data handling, not about the accuracy of the data: always check figures against the source.
Open the API Response to Table
Related tool pages
- JSON API Response to CSV: Turn the JSON from an API into a CSV file. Nested fields become columns and you pick which list to use. Free, in your browser, with an optional token.
How to use the API Response to Table: step by step
- Open API Response to Table.
- Enter the API address and choose how it checks who you are.
- Click Send request and, if there are several lists, pick the right one.
- Check the preview, then download CSV or Excel.
When to use the API Response to Table
- Turn a price or quote API response into an Excel sheet
- Export statement or transaction data for a reporting pack
- Check what an API returns before building a pipeline
- Prepare JSON for a pivot table
Example
Input: GET https://api.example.com/users + Bearer token
Output: id,name,address.city 1,Ada,London
Frequently asked questions
How does the token work?
Choose Bearer token, API key in a header, or username and password, and paste the value. It is sent only to the API you call, kept in memory, and never saved or written to Data Lineage.
What if the API blocks the request?
Some APIs do not allow calls from other websites (CORS), so the browser blocks the request. Use an API that allows browser access, or ask its owner to enable CORS.
What happens to nested fields?
Nested objects are flattened into columns such as address.city. If the response has several lists, you can choose which one becomes the table.
Is it safe to use a token in API Response to Table?
Your token goes only to the API you call, straight from your browser. Data Utility never receives it or the response. It is kept in memory for that request, not saved, and not written to Data Lineage. Use a token with read-only access when you can.
Tip: Keep ticker symbols and account codes as text. Excel can drop leading zeros or change a value such as 1E5 into a number.