Excel ExcelDNA C# / Try to copy Bloomberg BDH() behavior (writing Array after a web request)

Question

I want to copy Bloomberg BDH behavior.

BDH makes a web request and write an array (but doesn't return an array style). During this web request, the function returns "#N/A Requesting". When the web request finished, the BDH() function writes the array result in the worksheet.

For example, in ExcelDNA, I succeed to write in the worksheet with a thread.

The result if you use the code below in a DNA file, the result of

=WriteArray(2;2)

will be

Line 1 > #N/A Requesting Data (0,1)

Line 2 > (1,0) (1,1)

The last issue is to replace #N/A Requesting Data with the value and copy the formula. When you uncomment //xlActiveCellType.InvokeMember("FormulaR1C1Local", you are near the result but you don't have the right behavior

File .dna

 <DnaLibrary Language="CS" RuntimeVersion="v4.0">
<![CDATA[

using System;
using System.Collections.Generic;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Threading;
using ExcelDna.Integration;


    public static class WriteForXL
    {

        public static object[,] MakeArray(int rows, int columns)
        {
            if (rows == 0 && columns == 0)
            {
                rows = 1;
                columns = 1;
            }


            object[,] result = new string[rows, columns];
            for (int i = 0; i < rows; i++)
            {
                for (int j = 0; j < columns; j++)
                {
                    result[i, j] = string.Format("({0},{1})", i, j);
                }
            }

            return result;
        }

        public static object WriteArray(int rows, int columns)
        {
            if (ExcelDnaUtil.IsInFunctionWizard())
                return "Waiting for click on wizard ok button to calculate.";

            object[,] result = MakeArray(rows, columns);

            var xlApp = ExcelDnaUtil.Application;
            Type xlAppType = xlApp.GetType();
            object caller = xlAppType.InvokeMember("ActiveCell", BindingFlags.GetProperty, null, xlApp, null);
            object formula = xlAppType.InvokeMember("FormulaR1C1Local", BindingFlags.GetProperty, null, caller, null);

            ObjectForThread q = new ObjectForThread() { xlRef = caller, value = result, FormulaR1C1Local = formula };

            Thread t = new Thread(WriteFromThread);
            t.Start(q);            

            return "#N/A Requesting Data";
        }

        private static void WriteFromThread(Object o)
        {
            ObjectForThread q = (ObjectForThread) o;

            Type xlActiveCellType = q.xlRef.GetType();

            try
            {
                for (int i = 0; i < q.value.GetLength(0); i++)
                {
                    for (int j = 0; j < q.value.GetLength(1); j++)
                    {
                        if (i == 0 && j == 0)
                            continue;

                        Object cellBelow = xlActiveCellType.InvokeMember("Offset", BindingFlags.GetProperty, null, q.xlRef, new object[] { i, j });
                        xlActiveCellType.InvokeMember("Value", BindingFlags.SetProperty, null, cellBelow, new[] { Type.Missing, q.value[i, j] });             
                    }
                }                               
            }
            catch(Exception e)
            {                
            }
            finally
            {
                //xlActiveCellType.InvokeMember("Value", BindingFlags.SetProperty, null, q.xlRef, new[] { Type.Missing, q.value[0, 0] });
                //xlActiveCellType.InvokeMember("FormulaR1C1Local", BindingFlags.SetProperty, null, q.xlRef, new [] { q.FormulaR1C1Local });               
            }
        } 

public class ObjectForThread
        {
            public object xlRef { get; set; }
            public object[,] value { get; set; }
            public object FormulaR1C1Local { get; set; }
        }

    }

    ]]>

</DnaLibrary>

@To Govert

BDH has become a standard in finance industry. People do not know how to manipulate an array (even the Ctrl+Shift+Enter).

BDH is the function that made Bloomberg so popular (to the disadvantage of Reuters).

However I will think of using your method or RTD.

Thanks for all your work in Excel DNA

Rabblement

What does BDH() leave in cell (0,0) upon completion, a value or a formula? – Cursory

if you want the history of CAC 40 index =BDH("CAC Index";"PX_LAST";"20/05/2010";"20/05/2012"), you will have in cell(0,0) a value and in the formula =BDH("CAC Index";"PX_LAST";"20/05/2010";"20/05/2012";"cols=2;rows=478") 20/05/2010 3432.52 21/05/2010 3430.74 .... BDH respect all the standard of Excel EXCEPT when the data override existeing data in your sheet – Rabblement

OK - so in your function just write back a modified formula to cell (0,0), and have the function detect the modified parameters to return the value you want displayed there. I guess this is what BDH does when it sees the "cols=2;rows=478" as the last argument. – Cursory

Govert; Can you please elaborate further more. I have implemented similar feature as explained above using ExcelDna example. In my example I am returning "#Retriving Data" and then spawn thread which will get data from back end process; then use original excel Range to paste values in the caller location. But while doing so it looses the formulas. I want to keep the formula intact. How can I do this ? – Kujawa

Answer 1

My issue was :

• writing dynamic array

• data are retrieved asynchronous via a webservice

After discussing with Govert, I chose to take a result as an array and not to copy Bloomberg functions (write an array but return a single value).

Finally, to solve my issue, I used http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/ and reshape the resize() function.

This code is not RTD.

The code belows works in a .dna file

<DnaLibrary RuntimeVersion="v4.0"  Language="C#">
<![CDATA[
    using System;
    using System.Collections.Generic;
    using System.Reflection;
    using System.Runtime.InteropServices;
    using System.Threading;
    using System.ComponentModel;
    using ExcelDna.Integration;

    public static class ResizeTest
    {
        public static object[,] MakeArray(int rows, int columns)
        {
            object[,] result = new string[rows, columns];
            for (int i = 0; i < rows; i++)
            {
                for (int j = 0; j < columns; j++)
                {
                    result[i,j] = string.Format("({0},{1})", i, j);
                }
            }

            return result;
        }

        public static object MakeArrayAndResize()
        {         
            // Call Resize via Excel - so if the Resize add-in is not part of this code, it should still work.
            return XlCall.Excel(XlCall.xlUDF, "Resize", null);
        }
    }

    public class Resizer
    {
        static Queue<ExcelReference> ResizeJobs = new Queue<ExcelReference>();
        static Dictionary<string, object> JobIsDone = new Dictionary<string, object>();

        // This function will run in the UDF context.
        // Needs extra protection to allow multithreaded use.
        public static object Resize(object args)
        {
            ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
            if (caller == null)
                return ExcelError.ExcelErrorNA;

            if (!JobIsDone.ContainsKey(GetHashcode(caller)))
            {
                BackgroundWorker(caller);
                return ExcelError.ExcelErrorNA;
            }
            else
            {
                // Size is already OK - just return result
                object[,] array = (object[,])JobIsDone[GetHashcode(caller)];
                JobIsDone.Remove(GetHashcode(caller));
                return array;
            }
        }

        /// <summary>
        /// Simulate WebServiceRequest
        /// </summary>
        /// <param name="caller"></param>
        /// <param name="rows"></param>
        /// <param name="columns"></param>
        static void BackgroundWorker(ExcelReference caller)
        { 
            BackgroundWorker bw = new BackgroundWorker();
            bw.DoWork += (sender, args) =>
            {
                Thread.Sleep(3000);
            };
            bw.RunWorkerCompleted += (sender, args) =>
            {
                // La requete
                Random r = new Random();
                object[,] array = ResizeTest.MakeArray(r.Next(10), r.Next(10));

                JobIsDone[GetHashcode(caller)] = array;
                int rows = array.GetLength(0);
                int columns = array.GetLength(1);
                EnqueueResize(caller, rows, columns);
                AsyncRunMacro("DoResizing");
            };

            bw.RunWorkerAsync();
        }

        static string GetHashcode(ExcelReference caller)
        {
            return caller.SheetId + ":L" + caller.RowFirst + "C" + caller.ColumnFirst;
        }


        static void EnqueueResize(ExcelReference caller, int rows, int columns)
        {
            ExcelReference target = new ExcelReference(caller.RowFirst, caller.RowFirst + rows - 1, caller.ColumnFirst, caller.ColumnFirst + columns - 1, caller.SheetId);
            ResizeJobs.Enqueue(target);
        }

        public static void DoResizing()
        {
            while (ResizeJobs.Count > 0)
            {
                DoResize(ResizeJobs.Dequeue());
            }
        }

        static void DoResize(ExcelReference target)
        {
            try
            {
                // Get the current state for reset later

                XlCall.Excel(XlCall.xlcEcho, false);

                // Get the formula in the first cell of the target
                string formula = (string)XlCall.Excel(XlCall.xlfGetCell, 41, target);
                ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId);

                bool isFormulaArray = (bool)XlCall.Excel(XlCall.xlfGetCell, 49, target);
                if (isFormulaArray)
                {
                    object oldSelectionOnActiveSheet = XlCall.Excel(XlCall.xlfSelection);
                    object oldActiveCell = XlCall.Excel(XlCall.xlfActiveCell);

                    // Remember old selection and select the first cell of the target
                    string firstCellSheet = (string)XlCall.Excel(XlCall.xlSheetNm, firstCell);
                    XlCall.Excel(XlCall.xlcWorkbookSelect, new object[] {firstCellSheet});
                    object oldSelectionOnArraySheet = XlCall.Excel(XlCall.xlfSelection);
                    XlCall.Excel(XlCall.xlcFormulaGoto, firstCell);

                    // Extend the selection to the whole array and clear
                    XlCall.Excel(XlCall.xlcSelectSpecial, 6);
                    ExcelReference oldArray = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);

                    oldArray.SetValue(ExcelEmpty.Value);
                    XlCall.Excel(XlCall.xlcSelect, oldSelectionOnArraySheet);
                    XlCall.Excel(XlCall.xlcFormulaGoto, oldSelectionOnActiveSheet);
                }
                // Get the formula and convert to R1C1 mode
                bool isR1C1Mode = (bool)XlCall.Excel(XlCall.xlfGetWorkspace, 4);
                string formulaR1C1 = formula;
                if (!isR1C1Mode)
                {
                    // Set the formula into the whole target
                    formulaR1C1 = (string)XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell);
                }
                // Must be R1C1-style references
                object ignoredResult;
                XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlcFormulaArray, out ignoredResult, formulaR1C1, target);
                if (retval != XlCall.XlReturn.XlReturnSuccess)
                {
                    // TODO: Consider what to do now!?
                    // Might have failed due to array in the way.
                    firstCell.SetValue("'" + formula);
                }
            }
            finally
            {
                XlCall.Excel(XlCall.xlcEcho, true);
            }
        }

        // Most of this from the newsgroup: http://groups.google.com/group/exceldna/browse_thread/thread/a72c9b9f49523fc9/4577cd6840c7f195
        private static readonly TimeSpan BackoffTime = TimeSpan.FromSeconds(1); 
        static void AsyncRunMacro(string macroName)
        {
            // Do this on a new thread....
            Thread newThread = new Thread( delegate ()
            {
                while(true) 
                { 
                    try 
                    {
                        RunMacro(macroName);
                        break; 
                    } 
                    catch(COMException cex) 
                    { 
                        if(IsRetry(cex)) 
                        { 
                            Thread.Sleep(BackoffTime); 
                            continue; 
                        } 
                        // TODO: Handle unexpected error
                        return; 
                    }
                    catch(Exception ex) 
                    { 
                        // TODO: Handle unexpected error
                        return;
                    } 
                }
            });
            newThread.Start();
        }

        static void RunMacro(string macroName)
        {
            object xlApp = null;       
            try
            {
                xlApp = ExcelDnaUtil.Application;
                xlApp.GetType().InvokeMember("Run", BindingFlags.InvokeMethod, null, xlApp, new object[] {macroName});
            }
            catch (TargetInvocationException tie)
            {
                throw tie.InnerException;
            }
            finally
            {
                Marshal.ReleaseComObject(xlApp);
            }
        }

        const uint RPC_E_SERVERCALL_RETRYLATER = 0x8001010A; 
        const uint VBA_E_IGNORE = 0x800AC472; 
        static bool IsRetry(COMException e) 
        { 
            uint errorCode = (uint)e.ErrorCode; 
            switch(errorCode) 
            { 
                case RPC_E_SERVERCALL_RETRYLATER: 
                case VBA_E_IGNORE: 
                    return true; 
                default: 
                    return false; 
            }
        }
    } 
]]>
</DnaLibrary>

Rabblement

this code does not seem to work. MakeArray simply prints '(0,0)' in the cell. MakeArrayAndResize callls the Resize thread, but no value written to cells – Detonator

Answer 2

I presume you have tried the Excel-DNA ArrayResizer sample, which carefully avoids many of the issue you are running into. I'd like to understand what you see as the disadvantages of the array-formula-writing approach.

Now, about your function:

Firstly, you can't safely pass the 'caller' Range COM object to another thread - rather pass a string with the address, and get the COM object from the other thread (using a call to ExcelDnaUtil.Application on the worker thread). Most of the time you'll get lucky, though. The better way to do this is from the worker thread to get Excel to run a macro on the main thread - by calling Application.Run. The Excel-DNA ArrayResizer sample shows how this can be done.

Secondly, you almost certainly don't want the ActiveCell, but rather Application.Caller. The ActiveCell might well have nothing to do with the cell where the formula is running from.

Next - Excel will recalculate your function every time you set the Formula again - hence putting you in an endless loop when you enable the Formula set in your finally clause. You cannot set both the Value and the Formula for a cell - if a cell has a Formula then Excel will use the formula to calculate the Value. If you set the Value, the Formula gets removed. It's not clear what you want to actually leave in the [0,0] cell - IIRC Bloomberg modifies the formula there in a way that makes it remember how large a range was written to. You could try to add some parameters to your function that tell your function whether to recalculate or whether to return an actual value as its result.

Finally, you might want to reconsider whether the Bloomberg BDH function is a good example for what you want to do. It breaks the dependency calculation of your sheet, which has implications both for performance and for maintaining consistency of the spreadsheet model.

Cursory

"You cannot set both the Value and the Formula for a cell" Guess the trick would be to first return #N/A Requesting Data (0,1)" as the result of the formula, and when you're get excel to reevaluate the formula 'cell.Calculate()' and then return the "Done" or whatever. Keep a static flag that indicates wich should be displayed... bit of a hack but I guess it could work. (And THANKS for ExcelDNA!) – Ezmeralda

Answer 3

I think you need to implemented the request as a RTD server. Normal user defined functions will not update asynchronously.
Then you may hide the call of the RTD server via a user defined function, which can be done via Excel-DNA.

Haemachrome

Answer 4

So finally you use Array formula, right? As you said, users are not familiar with array formula, they do not know ctrl+shift+enter. I think array formula is a big problem for them.

For me, I have the same issue. I am trying to build a prototype for it. see https://github.com/kchen0723/ExcelAsync.git

Stanfield

This project is still in progress. some comments need to be added. – Stanfield