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.
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:
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.
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
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.