Databases
StackOverflow2013
Postsdbo
POExcel ExcelDNA C# / Try to copy Bloomberg BDH() behavior (writing Array after a web request)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel ExcelDNA C# / Try to copy Bloomberg BDH() behavior (writing Array after a web request)
    primarykey
    data
    text
    <p>I want to copy Bloomberg BDH behavior.</p> <p>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.</p> <p>For example, in ExcelDNA, I succeed to write in the worksheet with a thread.</p> <p>The result if you use the code below in a DNA file, the result of</p> <p>=WriteArray(2;2)</p> <p>will be</p> <p>Line 1 > <code>#N/A Requesting Data (0,1)</code></p> <p>Line 2 > <code>(1,0) (1,1)</code></p> <p><strong>The last issue</strong> is to replace <code>#N/A Requesting Data</code> 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</p> <p><strong>File .dna</strong></p> <pre><code> &lt;DnaLibrary Language="CS" RuntimeVersion="v4.0"&gt; &lt;![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 &amp;&amp; columns == 0) { rows = 1; columns = 1; } object[,] result = new string[rows, columns]; for (int i = 0; i &lt; rows; i++) { for (int j = 0; j &lt; 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 &lt; q.value.GetLength(0); i++) { for (int j = 0; j &lt; q.value.GetLength(1); j++) { if (i == 0 &amp;&amp; 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; } } } ]]&gt; &lt;/DnaLibrary&gt; </code></pre> <p><strong>@To Govert</strong></p> <p>BDH has become a standard in finance industry. People do not know how to manipulate an array (even the Ctrl+Shift+Enter).</p> <p>BDH is the function that made Bloomberg so popular (to the disadvantage of Reuters).</p> <p>However I will think of using your method or RTD.</p> <p>Thanks for all your work in Excel DNA</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USFarandole
    1. USFarandole
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExcel ExcelDNA C# / Try to copy Bloomberg BDH() behavior (writing Array after a web request)
      1. USGovert
      1. VTFavorite
    2. VO
      singulars
      1. POExcel ExcelDNA C# / Try to copy Bloomberg BDH() behavior (writing Array after a web request)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POExcel ExcelDNA C# / Try to copy Bloomberg BDH() behavior (writing Array after a web request)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat does BDH() leave in cell (0,0) upon completion, a value or a formula?
      singulars
      1. POExcel ExcelDNA C# / Try to copy Bloomberg BDH() behavior (writing Array after a web request)
      1. USGovert
    2. COif 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
      singulars
      1. POExcel ExcelDNA C# / Try to copy Bloomberg BDH() behavior (writing Array after a web request)
      1. USFarandole
    3. COOK - 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.
      singulars
      1. POExcel ExcelDNA C# / Try to copy Bloomberg BDH() behavior (writing Array after a web request)
      1. USGovert
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload