Quick Start
Knoema DataFinder allows you to automatically find and integrate relevant global data into your Excel spreadsheets with a single click. You have several options to get started depending on your data workflow; all start with you logging into the add-in.
To log in, go to Excel’s Data menu > click the DataFinder button on the right end of the toolbar > click Log in at the bottom of the right side panel.
Once you install the add-in to Excel, open the Knoema DataFinder pane by clicking the Data Finder button on the Data tab.
Option 1. Simply type into a cell the concept/query you are looking for, and hit enter. DataFinder will provide you with options in the right hand panel.
For example, type "US oil stocks", press Enter, then click the cell with your query in it. DataFinder will search for relevant data in Knoema's database and show the results in the right panel.
You can insert data directly into your spreadsheet or explore the data in the Dataset Viewer.
Click on any cell with a query and the DataFinder will refresh to show relevant data.
The contextual data in the DataFinder pane is related to the primary query. For example, if you pull the data for "crude oil price", you will also see prices for Brent, WTI, and Dubai benchmarks in addition to the average crude oil price.
To insert contextual data to the spreadsheet, click the title (for example, Brent), select an empty cell on the spreadsheet, and click Insert Data.
Option 2. From the Dataset Viewer:
-
Open Dataset Viewer and make your data selection.
Note: If you do not see the right panel with Tools, open your browser window in full screen mode OR look in the API menu along the top.
System note: Excel custom functions are currently not supported on iPad or in one-time purchase versions of Office 2019 or earlier. Read more here and here.
-
And then open Tools > API > Excel Formula.
Note: The formula includes the dataset ID, date and transform fields, and dimensions in the required sequence for your selection.
Two options are available to you from this window:
'Open in Excel' button
Once you click "Open in Excel", the file will download immediately as an Excel file. The file will contain both data and metadata for your selected time series because the file relies on the =Knoema.GetA() formula.
'Copy' buttonCopy the formula and enter it into a cell in your Excel workbook. Unlike the 'Open in Excel' option, this will copy only the data values for your selected time series using the =Knoema.Get() formula.
Note: You do not need to copy and paste this formula to all cells of the column. The results from the cell with the formula spill into the adjacent cells, which means that if additional data is added to the dataset, it will be automatically included with a refresh of your workbook.
To quickly add date labels to your new data, modify your formula to =Knoema.GetDates(rest of your formula) but remove any transform or transform placeholder (“ ”), which is in the third position in your formula. See “DataFinder Add-In Commands” below.
Option 3. Type into any cell =Knoema.Get and a prompt will appear showing you the formula options:
DataFinder Add-in Commands:
To fetch time series dates, use: =Knoema.GetDates(datasetId, dates, dimensions...)
To fetch time series data, use: =Knoema.Get(datasetId, dates, transform, dimensions...)
To fetch time series data with metadata, use: =Knoema.GetA(datasetId, dates, transform,dimensions...)
Watch Mode
Watch mode (eye icon) can be turned on and off. The DataFinder will only track your document/selection when Watch Mode is on. Watch Mode allows DataFinder follow or not to your text in the cells. Watch Mode is turned on when the eye icon is bright and DataFinder starts to search appropriate data regarding to your text in the cell (just click on it if you want to turn off the Watch Mode).
How it works when the Watch Mode is turned on:
How it works when the Watch Mode is turned off - DataFinder will not search the query you have typed, instead of this there will appear a separate search bar into it:
The Dates Parameter
DataFinder works with formulas to narrow and to customize your data range for a single series of data or to align all your data series to the same frequency, arrange, or array, and more. Following are the options available for specifying dates in the dates placeholder of your formula:
To fetch time series dates, use: =Knoema.GetDates(datasetId, dates, dimensions...)
To fetch time series data, use: =Knoema.Get(datasetId, dates, transform, dimensions...)
- All dates. Use empty double quotes “”.
- All dates according to Knoema accepted frequencies: A=Annual; H=half-year; Q=Calendar Quarterly; M=Monthly; W=Weekly; D=Daily.
- Cell range. Cell references must contain frequency or date in an accepted Knoema format and surrounded by double-quotes, e.g., YEAR, YEARQ#, YEARM#, MM/DD/YEAR.
Tip: Reference the results of Knoema.GetDates in your formula to keep the dates and data in sync.
- Date range. Type the range surrounded by double-quotes and in Knoema format, e.g. 2010-2019 or 2015Q1-2019Q4.
- An array of dates. Type the dates in an accepted Knoema format with NULL or ERROR for gaps, e.g. {"1948", "NULL", "1956"}.
The Transform Parameter
As you add data to your workbook with DataFinder, a variety of simple transformations are available to prepare your data for more in-depth analysis. Following are the available transforms you may use in the transform placeholder of your formula:
To fetch time series data, use: =Knoema.Get(datasetId, dates, transform, dimensions...)
- PCH – % change between two values - (v2-v1)/v1
- PCHY – % change from a year ago; e.g., a change from the same month of the previous year;
- PCHYTD – % change year to date
- PCHA – % change, annualized; a change from the previous month raised by 12 in the case of monthly data, and by 4 in the case of quarterly data;
- DIFF – Change; an absolute change from the previous values which represents value in the current period minus the most recent previous period;
- DIFFY – Change from one year ago;
- DIFFYTD – Change, YTD (year to date); an absolute change from the beginning of the year;
- DIFFQTD – Change, QTD (quarter to date); an absolute change from the beginning of the quarter;
- DIFFA – Change, YTD;
- DLOG – Log difference; the difference of natural logarithms of the current and previous period which is equivalent to the % change;
- DLOGY – Log difference from one year ago;
- DLOGYTD – Log difference, YTD;
- DLOGQTD – Log difference, QTD;
- DLOGP – Log difference, previous value;
- MOVAVGx Moving average (trailing) across N points;
- MOVAVGYx – Moving average, YoY (trailing) across N points;
- MOVSUMx – Moving sum (trailing) across N points;
- MOVSUMYx – Moving sum, YoY (trailing) across N points;
- SUMYTD – Sum of the values to year-to-date;
- SUMQTD – Sum of the value quarter-to-date;
- SUMYTDY – Sum, year-to-date, year-on-year;
- SUMQTDY – Sum, quarter-to-date, year-on-year;
- ABS – The absolute value of a number.
Tips & Troubleshooting
What is the dataset ID?
The dataset ID is a unique identifier that you can get from the Dataset Viewer by going to Tools > API > Excel Formula. The dataset ID is also part of the Dataset Viewer URL.
In the sample images below, the dataset ID is DSELECDRD2019. It’s also available from the webpage URL when you’re viewing the dataset.
How do I pull all of the data without specifying dates or transformations?
Skip the dates and/or transformations in the command by using opening and closing apostrophes “” as placeholders.
For example, a valid option without any transformations specified could look like: =KNOEMA.GET("DSELECPRD2019", "2015-2019", "", "KN.E47", "KN.N1")
How do I control whether the data is inserted as a column or a row?
From the add-in, access the settings menu (indicated with a cog icon in the lower right corner), and check or un-check ‘Insert time-series as columns’.
The data fetched by Knoema.Get comes without any labels, not even for different dates or locations. How do I know what’s what?
Knoema.Get works best when you create your own labels first for what you want, e.g., by specifically writing out the location name or date (or get the dates using Knoema.GetDates command), and then including those cells in your Knoema.Get formula.
This example shows data from the World Development Indicators (WDI) with dataset ID WBWDI2019Jan for a specific country (Australia) and indicator (GDP):
You can use the same approach of cell referencing to build out and parameterize your spreadsheet, e.g., by adding another country and referencing it in a second column:
I get a #NAME? error when I try to download data into Excel through DataFinder. What should I do?
You need to clear your Excel cache by deleting the contents of the following folder:
On Windows: %LOCALAPPDATA%\Microsoft\Office\16.0\Wef\
On Mac: ~/Library/Containers/com.microsoft.excel/Data/Library/Application Support/Microsoft/Office/16.0/Wef/
If you are experiencing unexplained error messages that prevent data from loading, we can provide you with step by step directions to collect error information to help troubleshoot. Please contact your system administrator for details.