Create C# DCOM Simple Server to Use Excel RTD function to Access Real Time Data for Excel Spreadsheet

• Download source code - 278.9 KB

Introduction

To pull off real time data into Excel spreadsheet, VBA has some disadvantages: such as hook up connection to Web Service; convert JSON string to collection, dictionary and variants; auto refresh set up, etc.

Using DCOM with C# and utilizing Excel RTD function provides the following benefits:

1. Make wide programming features and functions in .NET environment available to process data beyond VBA limitation.
2. Allow Excel its own calculation engine to handle data presentation and refresh.
3. Decoup VBA codes from Web access.

Step by Step to Create DCOM StockService

1. Start Visual Studio, create C# class library (.NET Framework) project, change default project name to StockService or whatever name you like. At project Properties/Build tab, check Register for COM interop. You run Visual Studio as administrator in order to compile project since it registers the COM object.
2. Change default class class1.cs to QuoteServer.cs (or any name).
3. Add COM reference Microsoft.Excel16.0 Object Library (or any other version available on your desktop).
4. After class name QuoteServer add IRtServer, automatically generate codes below:
   C#

   using Microsoft.Office.Interop.Excel;
   
   namespace StockService
   {
     Guid("93CF58DC-9381-4DA6-82D0-D7958C80045B"), // you need to generate 
                                                   // your own GUID
     ProgId("Stock.QuoteServer"),                  // and add these three lines 
                                                   // before class definition 
     ComVisible(true)
   ]
       class QuoteServer:IRtdServer
       {
           public int ServerStart(IRTDUpdateEvent CallbackObject){}
           public dynamic ConnectData
                  (int TopicID, ref Array Strings, ref bool GetNewValues)
           {}
           public Array RefreshData(ref int TopicCount){}
           public void DisconnectData(int TopicID){}
           public int Heartbeat(){}
           public void ServerTerminate(){}
       }
   }

   The remaining job is to implement those six methods.

5. Under the line QuoteServer:IRtServer, first place data elements into the class, and set up class constructor as follows:
   C#

   class QuoteServer:IRtdServer
   {
       private IRTDUpdateEvent rtdCallback;       // to hold object passed from Excel
       private Dictionary<int, Topic> myTopic;    // data element to store the
                                                  // information excel passed in s
       private DataSource myData;                 // data class we will create
                                                  // to store stock
       public QuoteServer()
       {
         myTopics = new Dictionary<int, Topic>(); //hold inputs from excel side
         myData = new DataSource();               //hold quote records
         myData.DataUpdated += MyDataUpdated;     //subscribe event from datasource
       }
   

6. Create Topic class as follows:
   C#

   internal class Topic
      {
          public int TopicId { get; set; }         //the value passed from Excel
          public string Symbol { get; set; }       //the value passed from Excel
          public string TopicType { get; set; }    //the value passed from Excel
      }
   

7. Create DataSource class and construct as follows:
   C#

   internal class DataSource {
          private static Dictionary<string, Quote> myQuotes =
                  new Dictionary<string, Quote>();       // to store stock infomation
          public event EventHandler<object> DataUpdated; // event handler to notify
                                                         // QuoteServer
          private Timer myTimer;
          public DataSource()
          {   myTimer = new Timer();                     // time to auto refresh
              myTimer.Interval = 30 * 1000;              // initial value set
                                                         // to 30 seconds
              myTimer.Elapsed += MyTimerElapsed;         // set Timer process
              myTimer.Start();                           // start timer
          }}
   

8. Create Quote class as below:
   C#

   internal class Quote
      {
          [DataMap("Price")]                             //Will discuss
                                                         //[DataMapAttribute later
          public double regularMarketPrice { get; set; } //property name need to
                                                         //match Yahoo finance API
          [DataMap("Change")]
          public double regularMarketChange { get; set; }
          [DataMap("Trade Time")]
          public double regularMarketTime { get; set; }
          [DataMap("ChangePercent")]
          public double regularMarketChangePercent { get; set; }
         // other members omitted here.....
      }
   

   In this class, property names need to match JSON node names returned from Yahoo financial API. You do not need to define all properties on that API. I use NewtonSoft.Json library to deserialize JSON string to Quote class.

9. Create DataMapAttribute custom attribute class to facilitate the processing Quote class data members. Below is the full implementation.
   C#

   internal class DataMapAttribute: System.Attribute {
          public DataMapAttribute(string name)
          {
            Name = name;
          }
          public string Name { get; set; }
          public DataMapAttribute(){}
          public static DataMapAttribute GetAttriubute(PropertyInfo fi)
          {
              var atr = fi.GetCustomAttributes(typeof(DataMapAttribute), false);
              if (atr.Length > 0)
                  return atr[0] as DataMapAttribute;
              return null;
          } }
   

10. Then, let us set up six methods of QuoteServer that implement Excel IRtdServer interfaces.
    C#

    //1.
    public int ServerStart(IRTDUpdateEvent CallbackObject){
        rtdCallback = CallbackObject;
        return 1;}
    //2.
    public dynamic ConnectData(int TopicID, ref Array Strings, ref bool GetNewValues)
    {
       if (Strings.Length<2)
          return "Two Parameters required"; // return value to Excel front end
      string symbol = Strings.GetValue(0).ToString();
      string topicType = Strings.GetValue(1).ToString();
      var topic = new Topic() 
      { TopicId = TopicID, Symbol = symbol, TopicType = topicType };
      myTopics[TopicID] = topic;
      var ret = myData.GetQuoteData(symbol, topicType);
      GetNewValue=true;
      return ret;
    }
    //3
     public Array RefreshData(ref int TopicCount) // call by Excel. 
                             // return data 2 rows by multiple column array
     {
        object[,] data = new object[2, this.myTopics.Count];
        int index = 0;
        foreach (var item in myTopics)
        {
         data[0, index] = item.Key;
         data[1, index] =  
              myData.GetQuoteData(item.Value.Symbol, item.Value.TopicType);
         index++;
        }
        TopicCount = myTopics.Count;        //update Excel side topic count
        return data;
       }
    //4 
    public void DisconnectData(int TopicID) // call by Excel happened 
                                            // when formular changed
     {
      var symbol = myTopics[TopicID].Symbol;
      myTopics.Remove(TopicID);
     } 
    //5 
    public int Heartbeat()                   // call by Excel every 15 seconds 
                                             // to keep server 
                                             // and connection alive
    {
       return 1;                             // just return 1 
    } 
    //6
    public void ServerTerminate()            // call by Excel when Excel application
                                             // exit  
    {
        myTopics.Clear();                    // clear up local data goes here 
    }

    We add additional method in QouteServer class to implement RTDCallback.UpdateNotify method:

    C#

    private void MyDataUpdated(object sender, object arg)
     {
      if (rtdCallback != null)        //rtdCallback is passed from Excel
          rtdCallback.UpdateNotify(); //here, notify Excel we have data updated
     }
    

11. On DataSource class. We implement a YFConnect class to pull stock quote via Yahoo Finance API:
    C#

     internal class YFConnect
     {
      const string YahooUrl = 
            "https://query1.finance.yahoo.com/v7/finance/quote?symbols=";
      internal async System.Threading.Tasks.Task<List<Quote>> 
               GetQuoteAsync(string symbol)
      {
      string url = $"{YahooUrl}{symbol}";
      using (var client = new HttpClient())
      {using (HttpResponseMessage response = await client.GetAsync(url)) 
      {
       try
        {
          response.EnsureSuccessStatusCode();
          string responseBody = await response.Content.ReadAsStringAsync();
          JObject data = JObject.Parse(responseBody);
          var result = data.Descendants()
             .OfType<JProperty>()
             .Where(p => p.Name == "result")
             .First()
             .Value;
           var results  = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Quote>>
                          (result.ToString());
              return results;
         }
          catch (Exception ex)
         {
          Console.WriteLine(ex.ToString());
        }
        }
      }
      return null;
     }
    }

    Here, I use Newtonsoft.Json library to deserialize JSON string from Yahoo Finance API. The library is included in the attached zip file. You may need Nuget if there are version issues.

12. At DataSource class, we set up two situations calling this class. One is at the case when Excel initializes query via ConnectData method. The other is at Timer Elapsed event, and DataSource pull data via Yahoo API at interval defined by the timer object. Then DataSource will raise DataUpdated event as follows:
    C#

    private void MyTimerElapsed(object sender, ElapsedEventArgs e)
    {
       string symbols = string.Empty;
       foreach (var quote in myQuotes)   //get all symbols from local data 
       {
         symbols += quote.Key + ","; 
       }
      var connect = new YFConnect();
      var data  = Task.Run<List<Quote>>(async () => 
                  await connect.GetQuoteAsync(symbols));
      var list = data.Result;
      foreach (var item in list)
      { 
        myQuotes[item.Symbol] = item;    // use new data to update old data
      }
      if(this.DataUpdated!=null)
      {
       DataUpdated(this, "DateUpdated"); // notify data change. 
                                         // This event is subscribed 
                                         // by QuoteServer class.
      }
     }

13. Set up Excel Spread sheet to pull data over. The Excel uses RTD function as cell formular to subscribe the StockServer. The syntax is below:

    =RTD("Stock.QuoteServer",,"IBM","Price")
    
    =RTD("Stock.QuoteServer",,"IBM","Change")

    When running Server on different machine, you need to provide that machine name as second parameter:

    =RTD("Stock.QuoteServer","OtherMachineName","IBM","Price") ....

    To more efficient process, I also implement as subscribe all data elements in one call with formular like this:

    =RTD("Stock.QuoteServer",,"IBM","All")

    This formular will get all data elements of a quote accumulating into a string, and place it in a cell. Each field is separated by a delimiter. At the backend, it is implemented as follows:

    C#

    static internal class utitlity
    {
      public static object ToValue<T>(this T t, string topicType)
      {
        if (t == null)
           return null;
        string allValue = string.Empty;
         PropertyInfo[] props = typeof(T).GetProperties();
            foreach (PropertyInfo prop in props)
            {
              var v1 = DataMapAttribute.GetAttriubute(prop);
              if ((v1 != null && v1.Name.ToUpper() == topicType.ToUpper()) || 
                   prop.Name.ToUpper() == topicType.ToUpper())
               return prop.GetValue(t);
              if (v1 != null && topicType.ToUpper() == "ALL")
              {
               allValue += prop.GetValue(t) + "|";
              }
            }
                return allValue;
         }
     }

    In the Excel spreadsheet, you need to write a VBA function to parse this string like this:

    VBScript

    Function ParseText(v As String, i As Integer) As Variant
        If v = Empty Then Exit Function
        Dim arr() As String
        arr = Split(v, "|")
        If i > UBound(arr) Then Exit Function
        ParseText = arr(i)
    End Function

Points of Interest

Microsoft did not update VBA for a long time. For programmers in the C/C++ or C# world, VBA programming is not fun. With RTD function, a lot of potential and features in other languages can be realized in Excel and VBA.

History

• 2^nd October, 2022: Initial version
• 4^th October, 2022: Updated source code zip file