# Setting up API datalink DIRECT to Column in Excel .XLSX **URL:** \ **Category:** Community\ **Created:** [January 1, 2020, 7:52pm UTC](https://dbnomics.discourse.group/t/setting-up-api-datalink-direct-to-column-in-excel-xlsx/307 "2020-01-01T19:52:26Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![MT\_MANC](https://avatars.discourse-cdn.com/v4/letter/m/57b2e6/32.png) [@MT\_MANC](https://dbnomics.discourse.group/u/MT_MANC)\ **Post date:** [January 1, 2020, 7:52pm UTC](https://dbnomics.discourse.group/t/setting-up-api-datalink-direct-to-column-in-excel-xlsx/307/1 "2020-01-01T19:52:27Z")
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](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 ---
**Author:** ![johan](https://yyz2.discourse-cdn.com/free1/user_avatar/dbnomics.discourse.group/johan/32/66_2.png) [@johan](https://dbnomics.discourse.group/u/johan)\ **Post date:** [January 2, 2020, 11:02am UTC](https://dbnomics.discourse.group/t/setting-up-api-datalink-direct-to-column-in-excel-xlsx/307/2 "2020-01-02T11:02:27Z")
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](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 ---
**Author:** ![MT\_MANC](https://avatars.discourse-cdn.com/v4/letter/m/57b2e6/32.png) [@MT\_MANC](https://dbnomics.discourse.group/u/MT_MANC)\ **Post date:** [January 2, 2020, 1:20pm UTC](https://dbnomics.discourse.group/t/setting-up-api-datalink-direct-to-column-in-excel-xlsx/307/3 "2020-01-02T13:20:20Z")
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](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** ![MenuChoice1_ParserDialogue](https://global.discourse-cdn.com/free1/uploads/dbnomics/original/1X/ea53ea13d05ffb41a2064ee8f678b672bc62fffc.jpeg) 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 !!): ---
**Author:** ![MT\_MANC](https://avatars.discourse-cdn.com/v4/letter/m/57b2e6/32.png) [@MT\_MANC](https://dbnomics.discourse.group/u/MT_MANC)\ **Post date:** [January 2, 2020, 1:20pm UTC](https://dbnomics.discourse.group/t/setting-up-api-datalink-direct-to-column-in-excel-xlsx/307/4 "2020-01-02T13:20:59Z")
Second screenhot: **MenuChoice2\_TableInsert Error.jpg** ![MenuChoice2_TableInsert%20Error](https://global.discourse-cdn.com/free1/uploads/dbnomics/original/1X/ed211550e28b879b5ad69df75281bc898bc7e26a.jpeg) ---
**Author:** ![MT\_MANC](https://avatars.discourse-cdn.com/v4/letter/m/57b2e6/32.png) [@MT\_MANC](https://dbnomics.discourse.group/u/MT_MANC)\ **Post date:** [January 3, 2020, 2:36pm UTC](https://dbnomics.discourse.group/t/setting-up-api-datalink-direct-to-column-in-excel-xlsx/307/5 "2020-01-03T14:36:11Z")
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” ) ![URL_CSV_WebAPI](https://global.discourse-cdn.com/free1/uploads/dbnomics/original/1X/ddf00a3c01fa48da1e35488e2c6fe141e9aeff0f.jpeg) 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 ? ---
**Author:** ![thomasbrand](https://avatars.discourse-cdn.com/v4/letter/t/9e8a1a/32.png) [@thomasbrand](https://dbnomics.discourse.group/u/thomasbrand)\ **Post date:** [January 6, 2020, 9:25am UTC](https://dbnomics.discourse.group/t/setting-up-api-datalink-direct-to-column-in-excel-xlsx/307/6 "2020-01-06T09:25:10Z")
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](https://db.nomics.world/OECD/QNA?dimensions=%7B%22LOCATION%22%3A%5B%22JPN%22%5D%2C%22FREQUENCY%22%3A%5B%22Q%22%5D%2C%22SUBJECT%22%3A%5B%22P31S14_S15%22%5D%7D&format=csv) for instance. - You can download each of the 15 series one by one by clicking on the download button below each. If you place your cursor above `Text file - csv`, you will see the corresponding link that you can copy paste in Excel. For instance : [https://api.db.nomics.world/v22/series/OECD/QNA/JPN.P31S14\_S15.CARSA.Q?observations=1&format=csv](https://api.db.nomics.world/v22/series/OECD/QNA/JPN.P31S14_S15.CARSA.Q?observations=1&format=csv) - You can download all the 15 series by clicking on the download button on the top right of the page. The corresponding link you obtain by placing your cursor above `Text file - csv` is for instance : [https://api.db.nomics.world/v22/series/OECD/QNA?limit=1000&offset=0&q=&observations=1&align\_periods=1&dimensions={"LOCATION"%3A["JPN"]%2C"FREQUENCY"%3A["Q"]%2C"SUBJECT"%3A["P31S14\_S15"]}&format=csv](https://api.db.nomics.world/v22/series/OECD/QNA?limit=1000&offset=0&q=&observations=1&align_periods=1&dimensions=%7B%22LOCATION%22%3A%5B%22JPN%22%5D%2C%22FREQUENCY%22%3A%5B%22Q%22%5D%2C%22SUBJECT%22%3A%5B%22P31S14_S15%22%5D%7D&format=csv) ---
**Author:** ![MT\_MANC](https://avatars.discourse-cdn.com/v4/letter/m/57b2e6/32.png) [@MT\_MANC](https://dbnomics.discourse.group/u/MT_MANC)\ **Post date:** [January 8, 2020, 4:21pm UTC](https://dbnomics.discourse.group/t/setting-up-api-datalink-direct-to-column-in-excel-xlsx/307/7 "2020-01-08T16:21:14Z")
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 ? ---
**Author:** ![thomasbrand](https://avatars.discourse-cdn.com/v4/letter/t/9e8a1a/32.png) [@thomasbrand](https://dbnomics.discourse.group/u/thomasbrand)\ **Post date:** [January 9, 2020, 8:20am UTC](https://dbnomics.discourse.group/t/setting-up-api-datalink-direct-to-column-in-excel-xlsx/307/8 "2020-01-09T08:20:28Z")
Hi, I try to answer : - extraction dates : not possible yet - interpolate : we built a tool here [https://editor.nomics.world/filters](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 : - We didn’t spend much time on developing functions for Excel users yet. We plan to do so, but it is quite time-consuming. - For some specific needs you have (rescaling, filtering by dates, etc.), it appears that you can do it much more efficiently using the R package for instance ([https://cran.r-project.org/web/packages/rdbnomics/vignettes/rdbnomics.html](https://cran.r-project.org/web/packages/rdbnomics/vignettes/rdbnomics.html)). ---
**Author:** ![MT\_MANC](https://avatars.discourse-cdn.com/v4/letter/m/57b2e6/32.png) [@MT\_MANC](https://dbnomics.discourse.group/u/MT_MANC)\ **Post date:** [January 9, 2020, 5:30pm UTC](https://dbnomics.discourse.group/t/setting-up-api-datalink-direct-to-column-in-excel-xlsx/307/9 "2020-01-09T17:30:29Z")
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”}}]](https://editor.nomics.world/series?source=dbnomics&series_id=IMF%2FWEO%2FABW.BCA&filters=%5B%7B%22code%22:%22interpolate%22,%22parameters%22:%7B%22frequency%22:%22quarterly%22,%22method%22:%22spline%22%7D%7D%5D) …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](https://api.db.nomics.world/v22/series?observations=1&series_id=IMF%2FWEO%2FABW.BCA&filters=%5B%7B%22code%22:%22interpolate%22,%22parameters%22:%7B%22frequency%22:%22quarterly%22,%22method%22:%22spline%22%7D%7D%5D&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”}}]](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=%5B%7B%22code%22:%22interpolate%22,%22parameters%22:%7B%22frequency%22:%22quarterly%22,%22method%22:%22spline%22%7D%7D%5D) **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](https://api.db.nomics.world/v22/series/IMF/WEO?limit=1000&offset=0&q=&observations=1&align_periods=1&dimensions=%7B%22weo-country%22:%5B%22JPN%22%5D%7D&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 !