The Analytics Edge Core Add-in supports both Quick Queries and Macro. Quick Queries are available with the Free license option, but Macros are only available with the Premium license.
- Select a cell
- Make a query
- Select Quick Query
- Click Write to Worksheet on the Functions wizard
You can place queries on the same worksheet or different worksheets, and repeat as often as you need*. Change the data you download, the accounts you select, and even the connectors you use. Click Refresh All to re-run all your queries.
When you make a new connector query, a popup will appear asking if you want a Quick Query or Macro. If you already have macros in the workbook, you may need to close the Task Pane to create a Quick Query.
* while Analytics Edge does not impose any restrictions on the number of queries you can make, Google, Facebook, Bing and other sources typically do. In normal usage, they should not be a problem.
Functions Wizard (Premium Feature)
The Core Add-in adds the ability to modify your results before writing them to your worksheet. When your connector query is finished, the Core Add-in will open a Functions Wizard allowing you to make these transformations to your data.To accept the data as-is from the connector, simply click Write to Worksheet.
You can optionally Pivot your results, Arrange the columns, Sort , and Filter the data before Write to Worksheet.
A temporary worksheet (ᴁ Step Results) highlighted in green will appear with the interim results, so you can see the impact of your selections on a sample of the results. When you click the Write to Worksheet button, the temporary worksheet will disappear and the full results will be written to your worksheet.
Edit a Query
You can modify a query by simply selecting the cell and clicking Edit Query. The previous settings are automatically inserted into the wizard, and you can make changes easily.
If the Edit Mode is ON, a comment will be placed in cells as a reminder of the query locations. While you are building your workbook, keep the Edit Mode ON. When you copy or move any of those cells (comments), the add-in will keep the queries in sync with the new cell locations.
When you are finished with changes, turn the Edit Mode OFF. In large workbooks, the Edit Mode can slow down Excel while it is on.
Note: the comment contains a pointer to the query, not the actual query. With the Edit Mode ON, you can copy or move the comment within the same workbook and the add-in will update the query for you. Removing the comment will NOT remove the query. Use the Cut button (scissors icon) to delete a query.
Format Your Results
The results are written to the worksheet, overwriting any previous content. You can format the results and subsequent refreshes will not alter the formatting — you are free to put your own personal look to the data.
Mind the Gap
Since some queries can change the number of rows or columns returned between refreshes, the Add-in will clear the previous ‘block’ of data before writing new results to the worksheet. Keep an empty column and row between your query results and other things on your worksheet, or the Add-in may wipe out more than you expected when you refresh.
You can add as many queries to your worksheet as you want. Click Refresh All to re-run all the queries [and macros] in the workbook. They will refresh in order: top-to-bottom, left-to-right.
Cut, Copy and Paste
If you want to copy or move any of the queries, simply turn on the Edit Mode and copy or move the comments. If you want to copy or move the Quick Query to a different worksheet or workbook, the Edit Query button has a drop-down menu with Cut, Copy and Paste functions.
Analytics Edge macros allow you to string together a series of queries and functions. This allows you to quickly create a custom analysis without the need for manual steps or complicated formulas. if you select a
- Select a cell
- Make a query
- Enter a macro name
- Add functions from the ribbon bar
- Add File > Write to Worksheet function from the ribbon bar
Note that macros can also contain cell references, which leave comments when Edit Mode is On. If the Edit Query button is not enabled when you select a commented cell, open the Task Pane to see the macros in your workbook — there is a cell reference in one of the macro functions.
Macros are managed in the Task Pane. The simplest macro would consist of a query function plus a Write to Worksheet function. As you work with your data, a temporary worksheet, (ᴁ Step Results) highlighted in green, will appear with the interim results, so you can see the impact of your selections on a sample of the results. When you add a Write to Worksheet function, the temporary worksheet will disappear and the full results will be written to your worksheet.
See the full list of macro functions available in the list on the right.