Ask the API for TSV or CSV, then wrap WEBSERVICE in TEXTSPLIT. WEBSERVICE fetches the URL and returns the reply as one long string; TEXTSPLIT spreads that string across the grid. No add-in, no macro, no key where the API does not need one.
A working example, pasted into a single cell:
=TEXTSPLIT(WEBSERVICE("https://qrep-api.tigzig.com/v1/compare?symbols=MSFT,META,ORCL,GOOGL,AMZN&benchmark=SPY&start_date=2025-03-14&end_date=2026-09-14&risk_free_rate=0.05&format=tsv"),CHAR(9),CHAR(10),FALSE,0,"")CHAR(9) is the tab that separates columns and CHAR(10) is the newline that separates rows, which is why the request asks for format=tsv. Ask for a tabular format, never JSON - a formula has no way to unwrap it.
Two things that look like failure and are not. Excel may warn about active content: choose "Paste without active content". If the cell shows an error, click into the end of the formula and press enter again. Give it a few seconds; the call is live.
If you only want to look at the numbers, skip Excel. Paste the same URL into a browser. It saves a file, which opens in Notepad and pastes straight into a sheet. Swapping format=tsv for format=json reads better on screen.
Where this stops working, and no formula fixes either one.
- One cell is one request, and
WEBSERVICEis volatile - it recalculates on any change anywhere in the sheet. A few hundred of those cells fire at the same instant and most come back refused: why Excel WEBSERVICE formulas return 429. - One Excel cell holds 32,767 characters. A whole-file download can never arrive this way, whatever you do to the formula. Power Query is the route for a file; Python in Excel (xlwings Lite) is the route when you need pacing, retries or control over where the rows land.
The worked example is QRep, which compares securities against a benchmark and returns the QuantStats metric set for each. Its contract shapes the URL above: up to six symbols per call with the benchmark outside that count, risk_free_rate as a decimal fraction (0.05 means 5 percent, and 5 is rejected), and plain YYYY-MM-DD dates. Free, no key. Reference: https://www.tigzig.com/apis/qrep, and the tool itself is at https://www.tigzig.com/qrep.
The same pattern works against any endpoint that will return a tabular response. Free xlwings guides and templates: https://www.tigzig.com/xlwings-starter. Full write-up: https://www.tigzig.com/post/qrep-excel-webservice-81-metrics-sep2026.
Building something like this? How I work covers the rates, the availability and what I take on.