Excel: Dynamic Data Exchange API issue with formula execution

Question

Not sure if this is the right forum to ask this question, sorry if this is too specific (i.e. finance-y) for SO. I have not worked with excel-DDE before and I could not find anything online resembling my problem.

I am using TradeBase MX to get live bid quotes from exchanges. TBMX has an excel-DDE api to get e.g. apple's bid quote at xetra via =@TBMX|QUOTE!US0378331005.xtr.bid. I'd like to automatically generate this formula based on the ISIN US0378331005 in another cell. The problem is that I can generate said formula via =CONCATENATE("=@TBMX|QUOTE!", A1,".xtr.bid"), if cell A1 contains the ISIN, but excel will not execute the formula, i.e. it will not display the bid quote in the cell. It will merely show =@TBMX|QUOTE!US0378331005.xtr.bid as text in the cell. I have tried defining a VBA function for evaluation via

Public Function eval(s As String) As Variant
    eval = Evaluate(s)
End Function

but =eval(CONCATENATE("=@TBMX|QUOTE!", A1,".xtr.bid")) will not work properly either. This method will only update the quote once upon calculation, and only if I hard-code =@TBMX|QUOTE!US0378331005.xtr.bid in another cell, i.e. if the DDE-data has already been called by excel. Not sure how to get the desired functionality to work using a formula. I would like excel to execute the DDE-resquest after the formula is generated in the cell.

Is this only attainable via VBA macro, which generates the formula and assigns it via .formula = "=@TBMX|QUOTE!US0378331005.xtr.bid"? I am sorry that my problem lacks repeatability due to the specific software TBMX which I am using, but I am lost on what to try here, since I do not know enough about what's going behind the scenes with the DDE-request.

Answer 1

I have worked with using Bloomberg as my API and have used VBA to code formulas referencing the Bloomberg functions. Rather than use concatenate could you just change the value of whatever your other cell is as the concatenated version? for example, if the cell you wanted the bid price to show up in was A3, did you try:

Sheets("yoursheetname").Range("A3").value = "=@TBMX|QUOTE!" & Sheets("yoursheetname").Range("A1").value & "xtr.bid"

I'm not sure how you have the sheet set up but it might make more sense to reference the cell using R1C1 or if you are pricing an entire portfolio and need to fill down your formula you could use a lastrow reference.