Project Description
Excel VSTO add-in demo: retrieve and analyze stock history.

This project is an Excel 2007 VSTO add-in, and was created as a proof-of-concept to illustrate some of the features of VSTO. It was initially presented at the Excel Developer Conference in London, 2012.
It is a work in progress; the core functionality is there, but the add-in hasn't been polished, and is far from rock-solid yet.

Features

VSTO stocks intends to provide simple market analysis tools from within Excel. The long-term goal is to replicate the functionality of Google Finance.

The add-in functionality is exposed through a Custom Task Pane, where a Ticker Symbol (say, MSFT for Microsoft) and two dates can be entered:

VSTOStocks_Overview.png

When the Load button is pushed, the add-in calls a Yahoo finance web service to retrieve historical data for the selected Stock, and appends 2 worksheets to the workbook: a raw listing of the stock history and a chart of the Close value with multiple moving averages overlaid. The chart is produced on the fly without any reference to a source range: it contains its own data.

VSTOStocks_Chart.png

Stocks that have been loaded in the session appear in a List View in the Task Pane. Select a stock, enter a number of simulations to run (numbers above 1 million may keep your PC busy for a few minutes), and press Simulate:

VSTOStocks_ProgressBar.png

While the simulation is running, the Simulate button becomes disabled, and a progress bar appears on the Task Pane and remains visible until the simulation completes; at that point, a chart will be added to the Workbook, displaying a histogram representing the probable value after 100 days if $100 were invested in the selected stock.

Note: this is a very crude simulation, using re-sampling of the historical day-to-day changes; please don’t use this to invest your retirement savings – this is provided as an example of heavy computation, not as a good investment recommendation!

While the simulation is running, the Excel user interface remains perfectly responsive: the simulation is being run on a background thread, keeping the UI thread unencumbered. Note also that on a multi-core machine, the simulation will run in parallel, using as many cores as possible to complete faster.

Last edited Feb 6, 2012 at 5:45 PM by mathiasb, version 5