Stock Quote Add-In For Excel 2007

//
// Parago Media GmbH & Co. KG, J�rgen B�urle (jbaurle@parago.de)
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED 
// TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL 
// THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF 
// CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER 
// DEALINGS IN THE SOFTWARE.
//

#region Using Directives

using System;
using System.Globalization;
using System.IO;
using System.Net;
using System.Reflection;
using System.Runtime.InteropServices;
using Extensibility;
using Microsoft.Office.Interop.Excel;
using Microsoft.Win32;

#endregion

namespace ParagoStockQuote
{
   #region User-Defined Functions (UDFs) Interfaces

   [Guid("121C4556-130A-4060-8AD2-2827CB1D6969")]
   public interface IFunctions
   {
      object PSQ(Range Cell, [Optional] object InfoCode);
   }

   #endregion

   [Guid(Functions.TypeGuid)]
   [ProgId(Functions.TypeProgId)]
   [ClassInterface(ClassInterfaceType.AutoDual)]
   public sealed class Functions : IFunctions, IDTExtensibility2
   {
      #region Constants & Fields

      public const string TypeGuid = "AED302B6-6185-426A-BF07-62A86A795522";
      public const string TypeProgId = "ParagoStockQuote.Functions";
      public const string ClsIdKeyName = @"CLSID\{" + Functions.TypeGuid + @"}\";
      public const string ExcelAddInKeyName = @"Software\Microsoft\Office\Excel\Addins\" + Functions.TypeProgId;

      Application _excel;

      #endregion

      #region Initialization

      public Functions()
      {
      }

      #endregion

      #region COM Registration

      [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 = Registry.LocalMachine.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", 0, RegistryValueKind.DWord);
         key.Close();

			UpdateAddInManager(TypeProgId);
      }

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

			//Registry.CurrentUser.DeleteSubKey(Functions.ExcelAddInKeyName);
			Registry.LocalMachine.DeleteSubKey(Functions.ExcelAddInKeyName);
      }

		static void UpdateAddInManager(string typeProgId)
		{
			try
			{
				string section = @"HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options\";

				int i = 0;
				bool found = false;
				string key;
				string value;

				while(true)
				{
					key = "OPEN" + ((i > 0) ? i.ToString() : string.Empty);
					value = (string)Registry.GetValue(section, key, null);

					if(value == null)
						break;
					else if(value.Contains(typeProgId))
					{
						found = true;
						break;
					}

					i++;
				}

				if(!found)
					Registry.SetValue(section, key, "/A \"" + typeProgId + "\"");
			}
			catch { }
		}

		#endregion

      #region Overrides

      // NOTE: Hide base methods like for COM, so they will not show up in the 'Insert Function' dialog of
      // Excel; it is not possible to override the GetType method.

      [ComVisible(false)]
      public override bool Equals(object obj)
      {
         return base.Equals(obj);
      }

      [ComVisible(false)]
      public override int GetHashCode()
      {
         return base.GetHashCode();
      }

      [ComVisible(false)]
      public override string ToString()
      {
         return base.ToString();
      }

      #endregion

      #region IDTExtensibility2 Member

      public void OnAddInsUpdate(ref Array custom)
      {
      }

      public void OnBeginShutdown(ref Array custom)
      {
      }

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

      public void OnDisconnection(ext_DisconnectMode removeMode, ref Array custom)
      {
      }

      public void OnStartupComplete(ref Array custom)
      {
      }

      #endregion

      #region User-Defined Functions (UDFs)

      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);
         }
      }

      #endregion
   }
}