Setting up API datalink DIRECT to Column in Excel .XLSX

Hi
Are there any (technical) examples of how to initially set-up an (auto-update) API link from DB NOMICS to an Excel sheet column.
eg I want Col C11 in .XLSX to show latest OECD QNA quarterly data from 1973q1-Latest Q from
https://api.db.nomics.world/v22/series/OECD/QNA/JPN.P31S14_S15.CARSA.Q?observations=1

My efforts (Data-Get Data- From Other Source) saw a 2-column Table inserted in the .XLSX rather than the single column of data, so hoping this problem has been encountered before. ( I tried uploading a screenshot from Excel but was not allowed)
Once this datalink is set-up for a single Column, I need to replicate for all columns in an Excel sheet that read from OECD:QNA
Any pointers on setting API links gratefully received

Hi,

Thank you for your interest in DBnomics!

Using DBnomics in Excel is an exciting prospect which we would like to offer. Currently, we don’t officially support Microsoft Excel but it would seem it should be feasible out-of-the-box with the latest versions of the software (and using this method: https://support.office.com/en-us/article/import-data-from-external-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a)

Which version of Microsoft Excel are you using? Could you tell us more about your method and process, i.e. Excel menu and features (with screenshots would be ideal). Thanks!

Best regards,

Johan Richer
DBnomics Team

Johan
Thanks for reply & Excel help link which I previously read - but its not clear
which of the many API datafeed methods is best for DB NOMICS; and the Power Query JSON parser explanation is also a little opaque. (I think many users may benefit from an Excel demo of this working, as raw data is often pre-processed in Excel before going into any stats/econometrics package) I think we are just a few steps off a successful API datafeed import.
Most kind

----------Specific Answers:
Version - Excel 2019 on Win10
Example OECD Series I want to show in single column of an Excel sheet:
(https://api.db.nomics.world/v22/series/OECD/QNA/JPN.P31S14_S15.CARSA.Q?observations=1)

Menu Choice1 - “Data”-“From Web”-[Above URL] I then get to the Power Query editor to parse the JSON data but I get stuck trying to show the raw series DATA feed in a single column - see below: MenuChoice1_ParserDialogue.jpg


It would appear that there is maybe a “Json.Document…” line missing at the top of this dialogue cf other YouTube videos on JSON parsing in Excel. ? Maybe this might explain some of the problems occurring ?

Menu Choice2 - “Data”-“Get Data”- “From Other Source”-“From Web” - [Above URL]-Into Table(Convert), I managed only to insert a a 2-column Table into two new columns! - see next reply (as can only upload 1 screenshot !!):

Second screenhot:
MenuChoice2_TableInsert Error.jpg

Update:
Managed to create a .CSV API link via creating the URL query using the DB NOMICS Web API link:

"https://api.db.nomics.world/v22/apidocs#/default/get_series__provider_code___dataset_code___series_code_"

This URL string to extract OECD:QNA series JPN.P31S14_S15.CQRSA.Q now partially works:

"https://api.db.nomics.world/v22/series/OECD/QNA/JPN.P31S14_S15.CQRSA.Q?observations=1&metadata=true&format=csv&align_periods=true&complete_missing_periods=true&limit=1000&offset=0"

See .XLSX screenshot

The CSV format cuts out all the JSON parsing hassle as CSV parsing is far simpler in Excel, but I wonder could anyone help refining the URL query string further:

  • ensuring that it dumps over 1973Q1-Latest Q including missing values (what extra URL code needed ?)
  • avoiding dumping/showing the LeftHand “Quarters” column (what extra URL code needed ?)
  • can I pre-apply some scaling to the raw datafeed data (maybe using a Scaling Constant
    held in Column Header Rows (in red) - ie can the URL query code references or reads this Scaler CellRef somehow ?)

Ideally, I would like the unique datalink URL for each col being generated from the unique Provider, Dataset & Series Code, Scaling held in each Column Header - see Col D in screenshot
(eg “OECD”, “QNA”, “JPN.P31S14_S15.CQRSA.Q”, “2.5E-04” )

Grateful for any pointers on refining the URL query string further
Also wondering to what extent setting up individual column Web API datalinks (as above) may significantly slow down the .XLSX operation/recalculation ???..and if so, how to optimise the data feed/update ?

Hi,

If you look at this webpage : https://db.nomics.world/OECD/QNA?dimensions={"LOCATION"%3A["JPN"]%2C"FREQUENCY"%3A["Q"]%2C"SUBJECT"%3A["P31S14_S15"]}&format=csv for instance.

Thomas - thanks very helpful.
I understand the query string syntax a bit better now, but had a few specific follow-ups on URL query possiblities:

  • how to select specific extraction dates eg 1970Q1-LatestQ ? (& enforce these across different providers & datasets where required)
  • can we interpolate in the data extraction ? eg convert from Annual-Only data to Quarterly (either via “Averaging Out” or “Divide by 4” switch)
  • any way to dump series ID (eg “JPN.P31S14_S15.CQRSA.Q”) at the start of the Series Description string in Row1 Descriptor (CSV format)
  • any way to rescale the data BEFORE it is dumped/displayed ? eg Convert from Units to Billions before displaying ?
  • any way to return just data (with no forecasts to 2021q4 appended) on eg OECD: Econ Outlook extractions ?

I was hoping that I could directly dump/refresh quarterly data to a specific Excel column in output sheet by “live-reading” (on Web API link) a unique URL string in a header cell of that column in output sheet,
rather than indirectly via (a) dump/refresh a collection of ALL required series to a general “DB NOMICS” Excel sheet and then (b) use Excel internal lookups in a particular column to display data in output sheet.
I suspect the direct method is too ambitious here ?

Hi,

I try to answer :

  • extraction dates : not possible yet
  • interpolate : we built a tool here https://editor.nomics.world/filters
  • if I understand well, you want the series code to be at the top of the column of values ? We choose to put the series name instead for now, but we can modify it if you convince us.
  • no
  • no, these are raw data, exactly what you have from the OECD website

Two remarks :

Very helpful - thanks for clarifiying & the CRAN link.
Any ballpark timeframe on Excel (and Eviews) functionality ? (both would be very popular I think)

Interpolation in URL - I notice applying an interpolation filter in the TSE generates this “permalink”:
https://editor.nomics.world/series?source=dbnomics&series_id=IMF%2FWEO%2FABW.BCA&filters=[{“code”:“interpolate”,“parameters”:{“frequency”:“quarterly”,“method”:“spline”}}]
…so I tried converting this to a direct extraction URL to .CSV (with filters applied) from the above “permalink” code but syntax below needs tweaking (but how ?)
Attempt 1:
https://api.db.nomics.world/v22/series?observations=1&series_id=IMF%2FWEO%2FABW.BCA&filters=[{“code”:“interpolate”,“parameters”:{“frequency”:“quarterly”,“method”:“spline”}}]&format=csv
Attempt 2:
https://api.db.nomics.world/v22/series/IMF/WEO/ABW.BCA?observations=1&metadata=true&format=csv&align_periods=true&complete_missing_periods=true&limit=1000&offset=0&filters=[{“code”:“interpolate”,“parameters”:{“frequency”:“quarterly”,“method”:“spline”}}]

Group Interpolation in URL - if I wanted to dump say all IMF WEO Annual data for Japan to .CSV…
https://api.db.nomics.world/v22/series/IMF/WEO?limit=1000&offset=0&q=&observations=1&align_periods=1&dimensions={“weo-country”:[“JPN”]}&format=csv
…but interpolate ALL of it to Quarterly (via Spline), where/how would the filter code get appended (if it can be ?):

&filters=[{"code":"interpolate","parameters":{"frequency":"quarterly","method":"spline"}}]

My attempts in combining these two unsuccessful so far !