Click here to Skip to main content
15,868,034 members
Articles / Programming Languages / C#

Stock Quote Add-In For Excel 2007

Rate me:
Please Sign up or sign in to vote.
4.86/5 (7 votes)
26 Apr 2012CPOL4 min read 105.8K   4.8K   21   31
Creating an MSN-like Stock Quotes add-in for Excel 2007 using User-Defined Functions and Ribbons.

Installation Instructions

Before using the add-in you should check if the installation has been executed correctly. Open the Excel 2007 Options dialog and click the menu item Add-Ins. The list Active Add-ins should contain the "Parago.de Stock Quotes Function Add-In" (COM-Add-In) as well as "Parago.de Stock Quotes UI Add-In" (COM-Add-In).

Then open the Excel Add-In Manager dialog by pressing the button on the Add-Ins dialog window (see button on the bottom of the dialog). Then check if the Add-In "ParagoStockQuote.Functions" is available in the list and is marked. If not, add it to the list and mark it.

Introduction

This article describes how to implement a managed stock quote add-in for Excel 2007 in C# that behaves similar to the “MSN MoneyCentral Stock Quotes” add-in you can download from Microsoft (see “Excel 2003/2002 Add-in: MSN Money Stock Quotes”). The add-in is developed from scratch, not using Visual Studio Tools for Office (VSTO).

Once the add-in is installed and registered, you can use the user-defined function (UDF) named “PSQ” to retrieve a stock quote price, date, time, and name for a given stock symbol from the Yahoo! finance service.

The following formula samples show the usage of the PSQ function:

=PSQ(B4;'PRICE') or just =PSQ(A1)to retrieve the last price
=PSQ(B4;'NAME') to retrieve the company name
=PSQ(B4;'DATE') to retrieve the last price date
=PSQ(B4;'TIME') to retrieve the last price time 

The add-in also implements a custom ribbon of the new Office “Fluent” user interface. The following two screenshots show the add-in in action using the German version of Excel:

StockQuoteExcelAddIn/Screenshot1.png

StockQuoteExcelAddIn/Screenshot2.png

The custom ribbon (named “Stock Quotes”) offers an Update button to retrieve (to be more precise to recalculate) all stock related formulas at once.

The main objective of this composition is to show how easy you can create an Excel 2007 add-in using the new UI elements and especially user-defined functions. Therefore, to keep this task simple and straightforward, I omitted the setup project and the usually necessary COM shim project (see explanation below) for managed Office extensions.

Office Extensions

In the course of the years, Microsoft exposed a huge number of different Office extensibility mechanisms like COM add-ins, XLL extensions, smart tags and much more, that developers could use to create their own custom solutions.

The stock quotes add-in described in this article is a COM add-in, to be more exact, it is broken into two add-ins. One add-in provides the user-defined function “PSQ”, the other one is responsible for the UI elements (ribbon). This technique allows you to use the UDF without activating the UI.

The COM add-in is an ActiveX-DLL (in-process server) that implements the IDTExensibility interface and all COM add-ins must provide an implementation of those five interface methods: OnAddInsUpdate, OnBeginShutdown, OnConnection, OnDisconnection, OnStartupComplete. In our case we just provide empty methods with no specific logic, except for OnConnection. This override saves a given reference to the host application (Excel) for later use:

C#
public void OnConnection(object host, ext_ConnectMode connectMode, 
            object addInInst, ref Array custom)
{
   _excel = (Application)host;
}

In addition to the classic COM registration, the add-in must register with Excel or another Office application the extension is working with. To register the extension with Excel, the add-in should create a sub-key, using its ProgID as the name for the key, under the following location:

HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\ProgID

In our case the ProgIDs for the two COM add-ins are ParagoStockQuote.Functions and ParagoStockQuote.UI. The add-in registration provides more information values for Excel like a full description and a friendly name. In addition we specify the add-in LoadBehavior (0x03 for load on application start up).

In order to register the extension in the Registry, the add-in provides two static methods named RegisterFunction and UnregisterFunction. They are attributed with ComRegisterFunctionAttribute and ComUnregisterFunctionAttribute. The CLR calls these methods once the containing assembly is registered through utilities like RegAsm.

C#
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type t)
{
   RegistryKey key;
   key = Registry.ClassesRoot.CreateSubKey(Functions.ClsIdKeyName + "Programmable");
   key.Close();
   key = Registry.ClassesRoot.CreateSubKey(Functions.ClsIdKeyName + "InprocServer32");
   key.SetValue(string.Empty, Environment.SystemDirectory + @"\mscoree.dll");
   key.Close();
   key = Registry.CurrentUser.CreateSubKey(Functions.ExcelAddInKeyName);
   key.SetValue("Description", "Parago.de Stock Quotes Function Add-In for Excel 2007", RegistryValueKind.String);
   key.SetValue("FriendlyName", "Parago.de Stock Quotes Function Add-In", RegistryValueKind.String);
   key.SetValue("LoadBehavior", 3, RegistryValueKind.DWord);
   key.SetValue("CommandLineSafe", 1, RegistryValueKind.DWord);
   key.Close();
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type t)
{
   Registry.ClassesRoot.DeleteSubKey(Functions.ClsIdKeyName + "Programmable");
   Registry.ClassesRoot.DeleteSubKeyTree(Functions.ClsIdKeyName + "InprocServer32");
   Registry.CurrentUser.DeleteSubKey(Functions.ExcelAddInKeyName);
}

For more information about COM Interop and COM registration, I recommend the CodeProject.com article: Understanding Classic COM Interoperability With .NET Applications.

Stock Quote Data

The add-in calls the finance portal from Yahoo.com to retrieve the requested stock quotes. The following URL returns a comma separated string containing stock quote data: http://download.finance.yahoo.com/d/quotes.csv?s=MSFT&f=sl1d1t1n.

The query string parameters are “s” for the stock symbol and “f” for the returned stock data fields. You can find a list of available fields in the documentation of the Perl module “Finance::YahooQuote” (CPAN.org). So, using this source it’s a matter of a few lines of code to get stock quotes (see code and section “User-Defined Functions”).

User-Defined Functions

User-defined functions are not a big deal. They are just plain methods with or without optional parameters. Optional parameters are flagged with OptionalAttribute. The method for “PSQ” looks as follows:

C#
public object PSQ(Range Cell, [Optional] object InfoCode)
{
   string symbol = Cell.Value2 as string;
   string infoCode = (InfoCode is Missing) ? "PRICE" : InfoCode as string;

   if(string.IsNullOrEmpty(symbol) || string.IsNullOrEmpty(infoCode))
      throw new Exception();

   WebClient client = new WebClient();
   Stream data = client.OpenRead("http://download.finance.yahoo.com/d/quotes.csv?s=" + 
                                 symbol.Trim() + "&f=sl1d1t1n");
   StreamReader reader = new StreamReader(data);
   string content = reader.ReadToEnd();
   data.Close();
   reader.Close();

   string[] quote = content.Split(",".ToCharArray());

   switch(infoCode.Trim().ToUpper())
   {
      case "NAME":
         return quote[4].Replace("\"", "").Replace("\r", "").Replace("\n", "");
      case "DATE":
         return Convert.ToDateTime(quote[2].Trim("\"".ToCharArray()), 
                CultureInfo.InvariantCulture).ToShortDateString();
      case "TIME":
         return Convert.ToDateTime(quote[3].Trim("\"".ToCharArray()), 
                CultureInfo.InvariantCulture).ToShortTimeString();
      case "PRICE":
      default:
         return Convert.ToDouble(quote[1], CultureInfo.InvariantCulture);
   }
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Germany Germany
I’m a software developer based in Germany.

Homepage

Comments and Discussions

 
QuestionMessage Closed Pin
19-Apr-21 0:46
Joshua Babashola19-Apr-21 0:46 
QuestionMessage Closed Pin
19-Apr-21 0:41
Joshua Babashola19-Apr-21 0:41 
Question?Name Solved Pin
Member 113485182-Jan-15 9:59
Member 113485182-Jan-15 9:59 
QuestionName Error Pin
Member 113485182-Jan-15 9:30
Member 113485182-Jan-15 9:30 
Question#name error on updating Pin
Member 1134131029-Dec-14 4:22
Member 1134131029-Dec-14 4:22 
GeneralMy vote of 5 Pin
Member 1133367724-Dec-14 14:10
Member 1133367724-Dec-14 14:10 
QuestionNon US stock quotes Pin
Member 112174759-Nov-14 6:01
Member 112174759-Nov-14 6:01 
QuestionDate, Time, and Name not working Pin
Member 1054233322-Jan-14 10:22
Member 1054233322-Jan-14 10:22 
AnswerRe: Date, Time, and Name not working Pin
Member 1133714326-Dec-14 18:47
Member 1133714326-Dec-14 18:47 
QuestionCan't get it to work Pin
Member 1047257817-Dec-13 7:08
Member 1047257817-Dec-13 7:08 
AnswerRe: Can't get it to work Pin
Member 1077034723-Apr-14 11:23
Member 1077034723-Apr-14 11:23 
GeneralRe: Can't get it to work Pin
Jürgen Bäurle7-May-14 23:28
Jürgen Bäurle7-May-14 23:28 
GeneralRe: Can't get it to work Pin
Member 1096392522-Jul-14 7:17
Member 1096392522-Jul-14 7:17 
GeneralRe: Can't get it to work Pin
Member 1132996722-Dec-14 20:03
Member 1132996722-Dec-14 20:03 
Question#NAME? Pin
Member 1028998423-Sep-13 3:08
Member 1028998423-Sep-13 3:08 
AnswerRe: #NAME? Pin
Member 1037122230-Oct-13 13:37
Member 1037122230-Oct-13 13:37 
GeneralRe: #NAME? Pin
Member 1132971922-Dec-14 17:44
Member 1132971922-Dec-14 17:44 
GeneralRe: #NAME? Pin
taz607126-Dec-14 5:16
taz607126-Dec-14 5:16 
GeneralRe: #NAME? SOLVED Pin
Member 1134131029-Dec-14 12:09
Member 1134131029-Dec-14 12:09 
GeneralRe: #NAME? SOLVED Pin
Member 113485182-Jan-15 9:55
Member 113485182-Jan-15 9:55 
QuestionDon't work with office 2010 pro plus 32bit Pin
silkman920-Aug-13 1:14
silkman920-Aug-13 1:14 
Questioncryptographic failure when building project Pin
tophee13-Jan-13 3:26
tophee13-Jan-13 3:26 
AnswerRe: cryptographic failure when building project Pin
tophee13-Jan-13 4:31
tophee13-Jan-13 4:31 
Question64-bit Excel? Pin
billchurch7618-Dec-12 15:22
billchurch7618-Dec-12 15:22 
AnswerRe: 64-bit Excel? Pin
Jürgen Bäurle18-Dec-12 23:17
Jürgen Bäurle18-Dec-12 23:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.