Category Archives: Analytics Edge Standard Add-in

Scheduler Manager


click image to enlarge

This Analytics Edge utility is part of the Standard Add-in, and is used to manage all of the Analytics Edge scheduled refreshes. If can be launched from the Schedule Refresh wizard, and will open automatically following any scheduled refresh.

Contents can be sorted and filtered in multiple combinations, and selected workbooks can be bulk refreshed (Run Now), or have their schedules changed (Reschedule), Disabled, Enabled or Deleted. Select the workbooks by checking the boxes in the left column, then pick the action to be performed at the top and click the Go button. Individual schedules can be modified by clicking the edit link.

Individual workbooks can be opened by clicking on the File name. Columns can be sorted by clicking on the column titles. You can filter for characters in the file name by typing in the Filter box, and further restrict the view by selecting the Folder, Type or Status values. The view can be refreshed by clicking the refresh button.

Details of the most recent refreshes can be seen by clicking the Log button.

Files can be scheduled in bulk by clicking the +Add Scheduled Refresh and selecting multiple files. The refreshes will be scheduled 1 minute apart starting at the time selected.

Status Messages

Each scheduled refresh is assigned a status based on the results of the last attempted automatic refresh. Note that if you manually open and refresh a workbook, it will not update the status, but if you select the workbook and pick Run NowGo, it will update the status.

ok – the refresh completed without error.

New – the refresh has not yet been run to completion. Note: if the computer is restarted after the scheduled First Start date, the refresh will run even if it is not scheduled to run that day. It is assumed the schedule was missed.

#Running – the scheduled refresh is currently running. Note that the process may not be visible in Windows. If a scheduled refresh remains in this state for more than an hour, it may be necessary to reboot your computer to clear the background process. If you do this, note that Excel may disable the Analytics Edge add-in, and you may have to re-enable it manually.

?See Log (macro error) – the refresh did not report an ok status upon completion. Usually this is caused by a macro error. See the Log in case there is indication of the failure.

?See Log (refresh crash) – the refresh did not complete withing the timeout period and may have failed unexpectedly. Check the workbook and manually refresh to see what the problem could be.

?See Log (no response) – the refresh ended without reporting success or failure. Check the file and refresh manually to see whether there is a problem.

!Disabled – the scheduled refresh has been disabled and will not run until re-enabled. Use this state to pause refreshes without deleting them.

!Not logged in – Windows attempted to run the scheduled refresh but the user was not logged in at the time. Run the Refresh at your earliest convenience.

!Problem with Scheduled Item – the scheduled task is not recognized as an Analytics Edge Scheduled Refresh. Recreate the scheduled item or delete the task.

!Not an AnalyticsEdgeRefresh – the scheduled refresh is not a properly configured Analytics Edge refresh. Delete and re-create the refresh schedule.

!Excel cannot be found – the instance of Excel on your system has moved since the refresh was scheduled. You will need to reschedule the refresh.

!Not an Excel workbook – the scheduled refresh does not point to a supported Excel file type (xls, xlsx, xlsm, xlsb).

!File does not exist – the schedule points to a workbook file that has been moved or deleted. Note that it may not be possible to refresh workbooks on network drives.

!Queue File Locked – if the refresh was unable to get access to the refresh queue file after several attempts, the file may be locked by a failure of you may have too many refreshes scheduled at the same time. It may be necessary to reboot your computer to clear the file lock.

!Aborted – the refresh was somehow aborted and will need to be rerun.

!Error <message> – the refresh reported an error. Open the workbook and refresh manually to confirm the error was not intermittent (server/service unavailable).

Schedule Refresh

schedule-refresh-2This Analytics Edge wizard helps you to schedule unattended refreshes for the workbook you have open.

Conditions required for the refresh to occur:

  • your computer must be turned on
  • you must be logged in (screen lock/screen saver is ok)
  • the Analytics Edge Standard Add-in must be enabled and licensed in Excel
  • the workbook must be available but not opened

The Analytics Edge Standard Add-in includes a Scheduled Refresh that can be used to trigger unattended refreshes of specific workbooks. The Scheduled Refresh runs on your computer (not a server), so your computer must be turned on for it to work.

Only one schedule can be created per workbook. If you open the Schedule Refresh wizard from a workbook that already has a schedule, the wizard will edit the existing schedule.

Abort Refresh after # minutes

The scheduler will wait a period of time to allow a scheduled refresh to complete and prevent other refreshes from running. You can specify how long the scheduler should wait before abandoning the process and starting another refresh.

One-Time Refresh

Select the One Time radio button, select the First Run At date and time for the refresh, and click the Set button.

Daily Refresh

To refresh at a specific time every few days, select the Daily radio button, and enter the number of days between refreshes (every day = 1; every second day = 2). Select the First Run At date and time for the refresh, and click the Set button.

Weekly Refresh

To refresh on specific days of the week, select the Weekly radio button, select the days you want the refresh to occur on and the number of weeks between refreshes (e.g. every second Tuesday). To select multiple days, hold the Ctrl-key down while clicking on the days. Select the First Run At date and time for the refresh, and click the Set button.

Monthly Refresh

To refresh on a monthly basis, select the Monthly radio button, and select the months desired. If you want specific days of specific weeks of the month, select the ‘on’ radio button, select the weeks and days desired.

If you want to refresh on a specific day of the month (2nd of the month, every 3 months), then select the ‘days’ radio button and select the days desired. Hold the Crtl-key down to select multiple days or months.

Select the First Run At date and time for the refresh, and click the Set button.

Other Scheduled Refreshes

Any number of workbooks can be scheduled, and they can be managed individually from the Schedule Refresh wizard when the workbook is open. You can also launch the Schedule Manager to see all scheduled refreshes.

You may discover that the scheduled refreshes are trigger via the Windows Task Scheduler. If you manually edit the entries in the Task Scheduler directly, they may not run correctly.

Running Queries from Buttons

With v2.0.0 of the Analytics Edge Standard Add-in, it is now possible to run a specific Analytics Edge query from a button on your worksheet (the Analytics Edge Standard Add-in must be installed). This opens the door to interactive refreshes  and improved user experiences.

Starting with a workbook containing an Analytics Edge query, first select the cell containing the query. A comment box should pop up containing the name of the Analytics Edge Macro (e.g. Sheet1!$A$1):

CAUTION: This will ONLY work on worksheets with names that do NOT include spaces.

Add a Form Control Button to your worksheet and draw the button on your worksheet.


When prompted for the Macro name, enter AnalyticsEdge.RunMacro. Click OK.



With the button still selected (right click on the button if you need to select it), change the button name to match the name of the macro to be run when you click the button (remove any ! or $ characters, so Report!$C$15 becomes ReportC15). You enter the button name in the Name box, located to the left of the formula bar:


That’s it! Now you can just click the button to run the Analytics Edge macro!


Formatting Notes

Once installed and registered, the Analytics Edge Standard Add-in can be used to quickly refresh multiple queries in your workbook. Simply select a destination cell and make a query, then repeat. To refresh all of the queries, click the Refresh All button on the Analytics Edge ribbon.

Some things worth pointing out:

Formatting will be preserved. If you format the cells in Excel, a refresh should not change the format (including conditional formatting) you have done. Note: if the data changes from text, date or numeric to a different type, the cell formatting may be confused by the new data and you may need to reset it to General.

Table formatting will be preserved. If you format a range as an Excel table, a refresh should preserve the table formatting EXCEPT: added columns, rows or calculated values will be lost. The table will be auto-resized to the data coming from the query.

Existing data blocks will be cleared. Analytics Edge looks for an empty row or column to determine the limits of the existing data ‘block’ or table in the worksheet. Everything inside that block will be cleared before the new data is written to the worksheet.

No merged cells. Analytics Edge does not support writing data to merged cells in Excel. Trying to do this will cause an error.


Refreshing Queries

Refresh All Queries

Click the Refresh All button on the Analytics Edge ribbon to refresh all of your queries in the workbook at once.


Refresh Current Query

click image to enlarge

click image to enlarge

To refresh only one query, select the worksheet cell where the query is based and click the Refresh – Current menu item from the drop-down below the Refresh All button.

Refresh Selected Queries

click image to enlarge

click image to enlarge

If you want to select which queries to refresh, click the Refresh – Selected menu item from the drop-down on the Refresh All button. In the dialog that opens, select the queries you want to refresh and click OK.

Auto-Refresh Pivot Tables

click image to enlarge

click image to enlarge

Microsoft Excel does not automatically update Pivot Tables in your workbook when you bring in new data, forcing you to manually refresh them from the Data ribbon. Analytics Edge has an option (turned on by default) that will trigger Pivot Table refreshes automatically whenever an Analytics Edge Refresh occurs. You can turn this feature off from the Refresh – Options menu item.

Moving a Query

Move a Query in the Same Workbook

To move an Analytics Edge query, simply move the comment to another location in the same worksheet, or another worksheet in the same workbook.

Move to Another Workbook – Cut and Paste

To copy queries between workbooks, select the worksheet cell where the query is, then select Edit Query – Cut from the Analytics Edge ribbon.


Then select the new location and select Edit Query – Paste from the Analytics Edge ribbon.

Edit a Query

To edit a specific query, click on the cell where the query is located (remember a comment was placed in that cell so you could find it easily). Click on the Edit button on the Analytics Edge ribbon bar. The original query wizard will open with all of your previous settings.


Make changes as desired and save the query. See the new results in your worksheet.

Delete a Query

To delete an Analytics Edge query from your worksheet, you must select the cell where the query is located, then either delete the comment or select the Edit QueryCut menu item from the Analytics Edge ribbon bar.


Copy a Query

Copy in the Same Workbook

To make a copy of an Analytics Edge query, simply copy the comment to another location in the same worksheet, or another worksheet in the same workbook.

Copy to Another Workbook

To copy queries between workbooks, select the worksheet cell where the query is, then select Edit Query – Copy from the Analytics Edge ribbon.


Then select the new location for the copy and select Edit Query – Paste from the Analytics Edge ribbon.

Adding Queries

With the Analytics Edge Standard Add-in and optional Connectors, you can automate the download of data from a number of sources directly into Microsoft Excel worksheets. You can mix queries from multiple sources, and use different accounts for each query if you want; Analytics Edge makes it easy to refresh them all with a single click.

Here is a simple example of adding 2 Google Analytics queries to a worksheet. [images of Basic Add-in shown; Standard Add-in works the same]



click the image to enlarge

If you haven’t already logged in to the Google Analytics connector, you need to set up an account to use (see Accounts).

Make Your First Query


click the image to enlarge

Click on the Excel worksheet cell where you want your query results, and create a new Google Analytics query (see Your First Query).

See The Results


click the image to enlarge

The results will appear in the worksheet at the cell you had selected. Note there is a comment on the cell to remind you of the location of the query. Do NOT remove the comment or you will delete the query.

Make Another Query

click image to enlarge

click image to enlarge

Select a different cell to place the second query.

Create the new query and see the results in your worksheet.

Optional Free Trials

The Analytics Edge Standard Add-in comes with two free connectors by default, but you can also install free trials of many others with a simple click. You can start as many free trials as you want, but remember: the clock starts ticking right away so make sure you have to to evaluate them all.


Once the connectors have been installed, restart Microsoft Excel to activate them. They will automatically self-register for a fully functional trial period, and you can start using them right away.

Functions Wizard

Functions Wizard

The Analytics Edge Standard Add-in allows you to make several transformations to your data before you write it into your worksheet. Once your query has been created, a Functions Wizard pops up offering you the ability to Pivot, Arrange, Sort or Filter your data as well as options for when you Write To Worksheet.

When editing a query, any tabs that have non-default settings will be *highlighted* with asterisks so you know they contain special selections. If you change the query itself, some of these selections may need to be updated (such as selected columns or filter values).


Pivot Function

Columns to Keep – select and rearrange the columns you want to keep in the resulting table. Select ‘– No Pivot‘ to leave the data as-is.

Pivot to Columns – select a column to pivot, so a new column will be created for each unique value in the selected column.

Cell Values – select a column to be used for the values in the cells in the pivoted table.

Aggregation – select the aggregation to use when there are duplicate values for a specific cell position.


Arrange Function

No Change – do not change the order of the columns.

Arrange By Name – Use this option to rearrange the columns based on the names of the columns. This is useful when you get imported data that may have additional, missing, or out-of-order columns.

Arrange Selected Columns – Use this option tab to arrange the columns by the names of the columns themselves. Select the columns you want arranged, then the sorting options. Note: the columns are identified by the column letters, not the actual names themselves. This allows you to sort the results of the Pivot operation, which could result in changing column names.


Sort Function

Create one or more sorting rules which will be applied in sequence to order your data. If two rows are equal in value for the first rule, the next rule is used to determine the order. You can specify as many rules as required.

Add Sorting Rule – select a Column and sort order (Ascending/Descending), then click Add to add it to the Sorting Rules.

By Name – You can select columns by name or by position (default). This allows you to sort by columns with names that may change as a result of a Pivot operation.

Sorting Rules – you can change the order of the sorting rules, or remove any of the rules using the arrows and X buttons.


Filter Function

Add Filter Rule – select a Column and filter criteria, enter a value, then click Add to add it to the Filter Rules. Filters will be combined using AND logic (rows must pass all filters), and you can choose to Keep or Remove the matching rows. While typically used to remove unwanted data from a report, it can also be used to selectively keep rows of interest.

By Name – You can select columns by name or by position (default). This allows you to filter by columns with names that may change as a result of a Pivot operation.

Filter Rules – you can change the order of the filter rules, or remove any of the rules using the arrows and X buttons.


Write to Worksheet Function

You can choose to Write to Worksheet (replace the previous query results) or Append to Worksheet (added to the existing query results in the worksheet).

Do not include header – check this to prevent the column names from being included in the first row of the results written.

Do not clear cell formatting – this option will only change the cell values and not affect any cell formatting in place. Note that this can cause problems if the format of the cell does not match the data (numeric versus text versus date).

Keep both – When appending to the existing data in the worksheet, simply append all the new data to the existing data. This may results in duplicate rows.

Do not change existing rows – If the new data contains rows with matching text and date columns, only append rows with new combinations. This does not change the previous reported values.

Update to new values – If the new data contains rows with matching text and date columns, replace the existing numeric values with the new values. This will refresh the previously captured values.


* Analytics Edge Standard Add-in

Love the product? Leave a comment in the Google Analytics Partners Gallery or share

2018-01-22 Welcome to the all-new Analytics Edge Standard Add-in!  Big changes have been made, so if you have been using previous release, READ THE CHANGE NOTICE AT THE BOTTOM  — Mike Sullivan

Your first step is to Register the add-in you have installed. Just start Excel and click the Register button on the Analytics Edge ribbon!

Installation problems?

Click the Activate 30-Day License to start your free trial period. No payment is necessary and the product is fully functional. OPTIONAL: Enter your email address to stay informed of updates to the product and make it easier to reset or transfer your product licenses after computer updates.

Help Is Just a Click Away

Click the HELP! button on any wizard or on the ribbon bar menus for additional information about any specific function or wizard feature. If the information available doesn’t answer your question, you can look in the Analytics Edge Simply Free support Forum for assistance, or send an email to

query-commentStandard Add-in Operation

The Analytics Edge Standard Add-in works very much like the Analytics Edge Basic Add-in; it allows you to assign queries to cells in your worksheets. Simply select a cell and open a Connector wizard. The results of the query will be written into the worksheet starting with the selected cell. A comment will be placed on the cell as a reminder of the query location.

Note: the comment contains a pointer to the query. Copy, delete or move the comment within the same workbook and you will copy, delete or move the query.

The Standard Add-in also allows you to Pivot your results, Arrange the columns, Sort and Filter the results before Writing to a Worksheet. When your Connector query is finished, the Standard Add-in will open a Functions Wizard allowing you to make these transformations to 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 click the Write to Worksheet button, the temporary worksheet will disappear and the transformed results will be written to your worksheet.

Formatting your worksheet

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. CAUTION: if you change the query, you will be asked if you want to clear the formatting; this is useful if you want to insert a column and do not want to use the  previous formatting in the cells.

Refresh All, Current or Selected

Click Refresh All to re-run all the queries in the workbook. To refresh only the query for the selected cell, select the drop-down menu below the Refresh button and pick Current. To refresh several queries at once, pick Selected from the drop-down menu.

Edit the Query at Any Time

You can modify the query by simply selecting the cell and clicking Edit. The previous settings are automatically inserted into the wizard, and you can make changes or cancel.

cut-copy-paste-queriesCut, Copy and Paste

If you want to delete, copy or move any of the queries, simply delete, copy or move the comments. If you want to copy or move the query to a different workbook, the Edit Query button has a drop-down menu with Cut, Copy and Paste functions that work across workbooks.

Sync Comments and Queries

If you copy or move any queries, or if you insert columns or rows causing the queries to move, the comments will be out-of-sync with the new locations. Simply click on one of the query cells to re-synchronize them.



There are 2 big changes to version 3 over previous releases: 

1 – Comments

  • comments are now used ONLY to copy or move queries and cell references
  • comments can be turned off – use the Show/Hide button on the ribbon to toggle them on/off
  • when comments are ON, the interface may be slower to respond; comments are OFF by default
  • comments appear for all query locations and any cell references in your queries
  • if you move comments, the associated queries and cell references will adjust automatically
  • if you copy comments, the associated queries will be copied (cell references remain at the original location)
  • IMPORTANT: after a move or copy operation, SELECT A CELL in the worksheet to trigger the synchronization

2 – ᴁ Analytics Edge Queries Worksheet

  • a worksheet containing all your queries is now available for manual editing
  • the worksheet is hidden by default – you can Unhide it easily in Excel, or use the Show/Hide pull-down menu
  • the first ‘ᴁParameters’ column is controlled by the add-in, and each subsequent column is a separate query
  • some documentation will be released on the syntax in the near future, but it is recommended that you use the wizards to edit your query if you can’t figure out the various parameters are
  • query parameters can be manually edited, and you can use cell formulas if desired (formulas will not be affected if you use a wizard to edit the query later)
  • you can copy columns and edit parameters to make multiple, similar queries
  • query names (top row 1) should be in the format of Sheet1!A1  (<worksheet>!<cell>) and be consistent with the WriteToWorksheet_worksheet/_topleftcell values