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
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!
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
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 !!):
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 ?
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 ?
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.
Very helpful - thanks for clarifiying & the CRAN link.
Any ballpark timeframe on Excel (and Eviews) functionality ? (both would be very popular I think)