4

Reference: I've looked at the posting Open excel file with Mathematica.

My intention is to be able to use Excel's RTD capability in order to pull market data into Mathematica. To my knowledge, this can be done, at the least, by having Excel use the RTD link, then using NETLINK to grab the value from Excel. At some point, it would be nice to have Mathematica pulling real time data from an Excel RTD link, but for now the RTD link value is only updated once per day.

Needs["NETLink`"]
PullValue[file_String] :=
  Module[{excel, workbook, worksheet, RTDvalue},
   NETBlock[InstallNET[];
    excel = CreateCOMObject["Excel.Application"];
    If[! NETObjectQ[excel], Return[$Failed], excel[Visible] = True;
     workbook = excel@Workbooks@Add[];
     worksheet = workbook@Worksheets@Item[1];
     worksheet@Range["A1"]@Value = 
      "=ProphetX|QUOTE!'@CT@A?SettlementPrice'";
     RTDvalue = worksheet@Range["A1"]@Value;
     workbook@SaveAs[file];
     workbook@Close[False];
     excel@Quit[];]];
   LoadNETType["System.GC"];
  GC`Collect[]
    RTDvalue];

outputfile = "C:\\Temp\\demo.xlsx";
Quiet[DeleteFile[outputfile]];
PullValue[outputfile];

The =ProphetX|QUOTE!'@CT@A?SettlementPrice' is the RTD portion, which at this point is putting a value into the saved Excel file, which should be correct. Sometimes when dealing with RTD links, I've noticed that I need to use FormulaArray, but @value seems to be working fine here.

Print[RTDvalue] is outputting -2146826246, instead of the correct value of ~ 81.26

So, I am wondering how I can get those values RTD values, which have gone into an Excel cell, into Mathematica, like they did in the posted link. Also, any direction or thought on how I could eventually have the data coming into Mathematicain real time.

Community
  • 1
user8264
  • 141
  • 1
  • Does RTD and DDE do the same thing in Excel? If yes, please take a look at this post. Maybe you could do something like Dynamic[Refresh[ExcelRead["A2"], UpdateInterval -> 0]]. – Rod Jul 02 '13 at 20:53
  • I had looked at this post previously, but from the information I gathered, those commands require an ExcelLink package which costs ~ $250. Correct me if I'm wrong, because that would be a way to go about it. – user8264 Jul 09 '13 at 17:07
  • Also, RTD links and DDE links are a bit different. I don't know the technical details, but from what I remember, RTD links are a something which Microsoft more recently developed and are a bit more stable..The way in which information flows through those links is also different. – user8264 Jul 09 '13 at 17:09
  • It's not NetBlock, is NETBlock – Murta Feb 26 '15 at 15:34

0 Answers0