Generating Excel reports from your Google Analytics data using ShufflePoint is a snap. One of the ways to get data into Excel is to use the IQY files which the Query Tool can generate. These IQY files can be set to expire after a specified time period. This is a means of protecting your analytics data access. But if you have invested in creating a nice Excel report, and your IQY file has expired, it is necessary to update the keys in all of the data import queries in the workbook you created.
Unfortunately, the way IQY files work is that each time you add a new query Excel imports the IQY file settings into the new query. The reference back to the original IQY file is lost. And if you have two dozen queries in you workbook to manually fix, you wouldn't be too happy.
In order to address this issue, we created an Excel macro which replaces the authentication token in all queries within the active workbook. Follow these instructions to use the macro.
First, download this zip file and extract the file "ShufflepointExcel.bas" to a local or shared folder. I place mine in the Excel macros folder at:
c:\Program files\Microsoft Office\Office 11\Macros
To use the macro, first open your Google Analytics report workbook in Excel. Then in the menu go to "Tools/Macro/Visual Basic Editor". In the Visual Basic editor, you will see a project tree on the right side. Right click on the icon for your workbook and select "Import file..." and open ShufflepointExcel.bas. Then in the menu of the Visual Basic editor, goto "Tools/References..." and check "Microsoft Scripting Runtime" (see references).
In the Microsoft Visual Basic window menu, select "File/Close and return to Microsoft Excel".
To get an update IQY authentication token, login to www.shufflepoint.com, launch the Query Tool, and open the Excel tab. Set the time limit you desire for your new key, and then click "download". Save the IQY file and then open it is WordPad. Copy into the clipboard the long string AFTER the "key=". This is your authentication token.
Next, in the menu go to "Tools/Macro/Macros..." and in the Macros name list, select "ChangeWebQueryKey" and then click the "Run" button. When prompted for the new IQY key, paste from the clipboard (control-v) the new key you just obtained, and click "Ok".
The macro will update all the keys in all your queries and then post a message with a count of the number of queries which were modified.
Note: When you try to refresh a data query on your Microsoft Excel worksheet, you may receive the following error message:
"This Web query returned no data. To modify the query, click OK, click the name of the external data range in the name box on the formula bar, and then click Edit Query on the External Data toolbar."
If you get this warning, try clicking the "refresh all" button again. If you continue to have errors or if you data is not getting updated, please contact ShufflePoint support.
Finally, if you don't want to go through this token update process for your Excel reports, you have two options. The first option is to use an IQY file generated with not time limit. The other (more secure) option is to use Web Queries instead of IQY files for you data imports.
References
http://msdn.microsoft.com/en-us/library/aa164509%28office.10%29.aspx
The Microsoft Scripting Runtime Object Library
http://www.microsoft.com/downloads/details.aspx?FamilyId=C717D943-7E4B-4622-86EB-95A22B832CAA
Windows Script 5.6 for Windows XP and Windows 2000
http://www.shufflepoint.com/assets/zip/ShufflepointExcel.zip
ShufflePoint Excel macros