/quandl-excel-windows

Quandl Excel Addin for Windows

Primary LanguageC#

Quandl Excel Add-in for Windows

The Quandl Excel Add-In allows you to search through, find and download any of Quandl's millions of datasets directly from within Microsoft Excel. It's completely free; usage is unlimited and unrestricted. Currently this Add-in is limited to windows only as it uses features and functions which are only available on the windows version of excel.

Development

A few things that will make your excel development experience much easier:

  • Excel is single threaded
  • Use Async tasks and don't block with long running code. This will block the Excel UI due to it being single threaded.
  • When making calls to excel via ExcelDNA thing of Excel as the server and our ExcelDNA app as the client. Design your application as if you are making requests of excel which it may or may not fulfill. Also not that the Excel server can be busy (overloaded) due to its single threaded nature and you may need to wait and retry your call later to fulfill it.

Setup

  1. Download and install InstallSheild Limited Edition here
  2. Right click solution file and select Restore NuGet Packages (If you don't have NuGet, please install it at https://dist.nuget.org/index.html)
  3. Go to the project properties for Quandl.Excel.Addin
  4. Click on signing tab
  5. Click Create Test Certificate without a password
  6. Do steps 5-7 for Quandleild.Excel.Console
  7. Go to the project properties for Quandl.Excel.UDF.Functions
  8. Under Debug change the Start Action from Start Project to Start External Program
  9. Fill in the path to your chosen version of Excel in the Textbox
  10. Under Start Options in the Command Line Arguments text field enter Quandl.Excel.UDF.Functions-AddIn.xll
  11. Whitelist your development plugin in Excel
    1. Open Excel
    2. Click File -> Options -> Truste Center
    3. Click Trust Center Settings
    4. Click Trusted Locations -> Add New Location
    5. Enter the root directory of your project, example C:\Users\Developer\Projects\quandl-excel-windows\
    6. Ensure Subfolders of this location are also trusted is checked
    7. Click OK
  12. You should now be able to build the project.

Building a Release package

Following steps will create a setup package which works for both Microsoft Excel 32 bit and 64 bit.

Preparation

  1. Follow the instructions list in Development section above to setup the project and its basic dependencies.
  2. Copy this file [Microsoft .NET Framework 4.6.1 Web.prq](Microsoft .NET Framework 4.6.1 Web.prq) to folder C:\Program Files (x86)\InstallShield\2015LE\SetupPrerequisites
  3. Copy and say yes to overwrite both VSTO package .pqr files to folder C:\Program Files (x86)\InstallShield\2015LE\SetupPrerequisites
  4. Install Redistributables
    1. Start Visual Studio as Admin
    2. Expand the Quandl.Excel.Addin.Setup project
    3. Click 2 -> Specify Application Data -> Redistributables
    4. In the new window that opens, right click on any item an click Download All Required Items

Releasing

  1. Ensure the setup project is signed Quandl.Excel.Addin.Setup -> 6 Prepare for Release => Releases => SingleImage => Signing
  1. Navigate to Quandl.Excel.Addin.Setup -> 1 Organize Your Setup => General Information
  2. Change the product code (use the helper - {...})
  3. Bump the version number. * Be sure to leave the upgrade code untouched.
  4. Navigate to the Quandl.Excel.Addin -> Properties => Publish and update the version to match the setup version.
  5. Navigate to the Quandl.Shared.Modules -> Utilities => ReleaseVersion and update the version to match the setup version.
  6. Switch your Run Mode to release instead of debug
  7. Right click solution file and select Rebuild Solution
  8. Select the Quandl.Excel.Addin.Setup project and in the topbar InstallShield LE menu select Open release folder to find your setup.exe file.

Things to note:

  • UnRegisterAddin must have code 1501 in the .isl file.
  • Be sure to bump the version AND change your product code number under Organize Your Setup => General Information. This is necessary for a seemless upgrade.
  • Allow of our dependencies have been listed as web dependencies to keep our installer small.
  • we are using Markdown.XAML to generate the flowdocument from the github markup. For more info check out the github page.
  • When testing, if your plugin does not appear in Excel, check that it was not added to the Disabled Items list. To check:
    • Open Excel
    • Click File -> Options -> Add-Ins
    • Under the Manage dropdown, select Disabled Items and click GO
    • Enable any instance of the Quandl Add-In that appear there

Unit testing

See Unit Testing Guide

FAQ

For a list of excel exceptions and how to debug them please see: Errors

The VSTO Add-in and or UDF excel plugin is listed but not displaying/activating

Excel seems to a have a bug where even when you close all its windows it can leave the main process running in the background. This seems to be for quick preview reasons (when you click an excel file in explorer). When this happens excel unloads all its add-ins to save memory. However this also means that it won't reload them until next time its started properly. To reload the add-in you must forcibly close any remaing excel instances from the task manager details tab.

My UDF function was running along great but then appears to have stopped updating

This could be a number of things but generally means that our implementation has run into one of the following problems:

  • Unhandled excel exception - Excel has many mysterious exceptions that result in COMException errors. If left unhandled they can crash excel or stop the running UDF.
  • Threading deadlock - This can happen if we use our threads in a non thread safe way. Basically we are not handling a specific case properly.
  • Excel request deadlock - This can occur when excel is busy and we try to make another request to it from a different thread.
  • Unhandled server error response - Our server is having issues and after a few retries our code simply gives up.

InstallShield Limited Edition is not displaying the Redistributables

Double check your installshield limited edition settings. Sometimes it will point to the wrong redistributables folder even after re-installation. In most cases it should be set to C:\Program Files (x86)\InstallShield\2015LE\SetupPrerequisites

My installation failed!

Uninstall (if it got that far) then run the installer again from the command line with the parameter /debuglog This should create a log in the same directory as the installer named InstallShield.txt. This should show you what went wrong.

License

See LICENCE file for licence rights and limitations (MIT)