Streaming Excel DDE Link - Wealth Lab Forum

fastrade

#1

11/23/2010 4:29 PM

Reading the wiki page on this add in I got the impression that the DDE link can be used to feed WL pro. Is there a way of getting the streaming data from WL Pro and feed it to Excel? TIA

Eugene

#2

11/23/2010 5:03 PM

If you're talking about the DDE streaming provider, it definitely can not be used to stream data from WL Pro to Excel.

fastrade

#3

11/23/2010 7:29 PM

can one be created? is the API availalble?

Eugene

#4

11/24/2010 2:08 AM

No. "Forget about it" as they say.

dansmo

#5

11/24/2010 2:20 AM

Is there any other way to write to an open excel file from WLD6? In WL4 there was a solution for this:

CODE:
Please log in to see this code.

Later in the code it was possible to write directly to an excel sheet:

CODE:
Please log in to see this code.

How can this be done in WLD6?

Check out this FAQ item I've just added. It's at the bottom of this page right now:

Is there a way to read/write values to an open Excel sheet?

Shiver me timbers if I knew a way to implement what "fastrade" was talking about w.r.t. Fidelity data. No way.

dansmo

#7

11/24/2010 3:27 AM

Thank you for the links.

dansmo

#8

11/24/2010 8:04 AM

That was really "easy" to implement. For anyone interested here is a bacic example code how to write to an excel file:

CODE:
Please log in to see this code.

Size:

Color:

That was really "easy" to implement. For anyone interested here is a bacic example code how to write to an excel file:
[CODE]    class ExcelConnect: WealthScript
    {
        //Declare these two variables globally so you can access them            
        Excel.Application objApp;
        Excel._Workbook objBook;
		
		protected override void Execute()
		{
            Excel.Sheets objSheets;
            Excel._Worksheet objSheet;
            Excel.Range range;
            try
            {
                // Open a specified workbook           
                objApp = new Excel.ApplicationClass();
                objBook =  objApp.Workbooks.Open("C:/WLD-Temp/Test123.xls");
                objSheets = objBook.Worksheets;

//write to the 2nd sheet
                objSheet = (Excel._Worksheet)objSheets.get_Item(2);

range = objSheet.get_Range("A1");

//Set the range value:                
                range.set_Value(Missing.Value, "Greets");

//another value
                objSheet.get_Range("A2").set_Value(Missing.Value, "from WLD 6");

//Return control of Excel to the user.             
                objApp.Visible = true;
                objApp.UserControl = true;

//Save and Close
                objBook.Save();
                objBook.Close();
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);
                MessageBox.Show(errorMessage, "Error");
            }
        }
    }[/CODE]

hlh

#9

4/6/2011 8:04 AM

Could one be so kind to explain what is required to run above code example, i.e. being able to read and write from an existing Excel sheet (2003, 2007, 2010).

I don't know how to reference in WL6 to the Excel COM Interop, heck, I don't even know if the words I am using here are the right ones explaining my question. So I am getting error CS0246: The type or namespace name "Excel" could not be found (are you missing a using directive or an assembly reference)?

Adding 'using System.Office.Interop.Excel;' alone did not help, of course.

The Wiki article "Programming | Using COM Interop in WL6" did not help me as the external link is a way to complicated description for me and the mentioned example code in C# does not exist (yes, I was logged-in to Wiki).

Thank you!

Eugene

#10

4/6/2011 8:26 AM

This Excel stuff can get tricky at times so I understand the frustration. I stopped using Excel in favor of OpenOffice/LibreOffice long ago but will try to help.

First of all, installed Visual Studio Tools for Office from Microsoft is a prerequisite for making .NET apps "talk" to MS Office apps.

Having completed this step, my understanding is that WL should be restarted and the newly installed COM Automation library becomes available in the Wealth-Lab Editor's "References" dialog. Depending on Office version it will be labeled something like "Excel Object Library 11.0".

When it's referenced in the particular strategy, the Excel namespace should no longer produce the CS0246 error, I believe.

hlh

#11

4/6/2011 10:26 AM

I tried this now, but it doesn't show up in the Reference dialog. Maybe I installed the wrong version? I did install vstor40_x64.exe from http://www.microsoft.com/downloads/en/details.aspx?familyid=06C32242-2289-4471-93AA-CE96AA5CBC36&displaylang=en which is the 'Visual Studio 2010 Tools for Office Runtime' as I have VS 2010 installed.

Maybe as WL6 is based on .NET2, should I have tried a different/previous version? I guess older versions might not be compatibel with latest Excel 2010 (while at the moment I need to write to 2007 version)?

The reason I would need to write (ideally directly) from WL to an Excel file is because I generate signals which I forward to others. They somehow do not find it convenient when I am sending them ASCII based *.CSV files so I made a fancy looking Excel sheet with a bunch of autoformating (case date is not correct etc.).

Eugene

#12

4/6/2011 10:51 AM

Hmm, I've digged a bit deeper and if these "VS Tools" are COM Interop libraries, they won't appear on the Wealth-Lab Editor's ".NET framework" tab. (In VS, they should be available under the "COM" tab though.)

Let's try the following:

1. Using Explorer, find this file created by the VSTO installation: Microsoft.Office.Interop.Excel.dll. Either put it in the Wealth-Lab's main folder under Program Files or add a reference to the DLL from the "Other assemblies to reference" tab.

Edit
However, if VSTO installer adds them to the GAC, skip step #1 and just look for "Microsoft.Office.Interop.Excel" on the WL Editor's "NET Framework" tab and enable it.

2. Add this directive to your strategy code:

CODE:
Please log in to see this code.

P.S. Although WL6 is based on NET 2.0, strategy/indicator libraries, data providers and other addins can be built for .NET 3.5 (for example, Neuro-Lab, Monte Carlo/Heat Map visualizers etc.) If I'm not mistaken, developing strategies based on C# 2.0 syntax (i.e. no LINQ etc) is the built-in Editor's limitation, but even that won't stop from referencing a 3.5 assembly (References - Other assemblies to reference) and coding with it.

dansmo

#13

4/7/2011 1:20 AM

Btw, I have implemented above code in VS Express directly and was not working from WL´s editor.

Eugene

#14

4/7/2011 2:26 AM

You haven't tried or it didn't work for you for some reason?

dansmo

#15

4/7/2011 2:57 AM

I just did not try since my approach is now tto almost always write my code directly in VS Express. When it gets complicated it is much more convenient to write the code and debug it.

hlh

#16

4/7/2011 10:38 AM

So far, I got it to work in WL6, at least somehow:

QUOTE:
However, if VSTO installer adds them to the GAC, skip step #1 and just look for "Microsoft.Office.Interop.Excel" on the WL Editor's "NET Framework" tab and enable it.

This did not work as it did not show up in WLs References, despite the fact that I found it in the GAC - whatever mysterious the GAC might be (found it with "Everything", the best search tool in the (Windows) world, by far).

Then I figured that I had three Microsoft.Office.Interop.Excel.dll's on my PC. Why? I don't know! Mabye all three of them were installed with the vstor40_x64.exe installation? So I tried the newest under ...Visual Studio 10.0\...\Office14 - which did not work (I guess Office 14 = Office 2010 which I don't have installed yet). Then I tried to reference to ...Visual Studio 9.0\...\Office12 and I got it to work. Well, as I said, somehow:

I have add 'using Microsoft.Office.Interop.Excel;' but still I cannot get it to recognize that namespace (or however such things are called betweend developer when they sit together and laugh at normal human beings). So I still hat do enter things like 'Microsoft.Office.Interop.Excel.Application app;' instead of 'Excel.Application app;' for everything, but not for 'Workbook.book;', which is the only thing getting an error when I tried 'Microsoft.Office.Interop.Excel.Workbook book;'.

Okay, so be it. Or not. Later that night I figured (some remark in the www) that I better might change 'using Microsoft.Office.Interop.Excel;' to 'using Excel = Microsoft.Office.Interop.Excel;'!? But when I tried, tired as I was, it showed me other errors, so I don't know (yet) if this is the way to go.

But I had to modify the sample code from dansmo (thank you for posting it here - such things always help a lot), like:

CODE:
Please log in to see this code.

This was also mentioned on many websites, that to open one has to add those many strange things MS requests for Excel, using 'Type.Missing' helps to avoid setting all those parameters, at least for my test. '.Open(@"...")' alone as in dansmo's example did not work for me. I also had to use other commands to read/write in Excel. Again, I have Excel 2007 installed (and using to write in an xls document in prev. version 2003, so not an xlsx).

Size:

Color:

So far, I got it to work in WL6, at least somehow:

[QUOTE]However, if VSTO installer adds them to the GAC, skip step #1 and just look for "Microsoft.Office.Interop.Excel" on the WL Editor's "NET Framework" tab and enable it.[/QUOTE]

This did not work as it did not show up in WLs References, despite the fact that I found it in the GAC - whatever mysterious the GAC might be (found it with "Everything", the best search tool in the (Windows) world, by far).

Then I figured that I had three Microsoft.Office.Interop.Excel.dll's on my PC. Why? I don't know! Mabye all three of them were installed with the vstor40_x64.exe installation? So I tried the newest under ...Visual Studio 10.0\...\Office14 - which did not work (I guess Office 14 = Office 2010 which I don't have installed yet). Then I tried to reference to ...Visual Studio 9.0\...\Office12 and I got it to work. Well, as I said, somehow:

I have add 'using Microsoft.Office.Interop.Excel;' but still I cannot get it to recognize that namespace (or however such things are called betweend developer when they sit together and laugh at normal human beings). So I still hat do enter things like 'Microsoft.Office.Interop.Excel.Application app;' instead of 'Excel.Application app;' for everything, but not for 'Workbook.book;', which is the only thing getting an error when I tried 'Microsoft.Office.Interop.Excel.Workbook book;'.

Okay, so be it. Or not. Later that night I figured (some remark in the www) that I better might change 'using Microsoft.Office.Interop.Excel;' to 'using [b]Excel =[/b] Microsoft.Office.Interop.Excel;'!? But when I tried, tired as I was, it showed me other errors, so I don't know (yet) if this is the way to go.

But I had to modify the sample code from dansmo (thank you for posting it here - such things always help a lot), like:
[CODE]				// Open a specified workbook           
				app = new Microsoft.Office.Interop.Excel.ApplicationClass();
				book =  app.Workbooks.Open(@"C:\Test\Test.xlsx",
					Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing, Type.Missing, Type.Missing,
					Type.Missing, Type.Missing);
[/CODE]
This was also mentioned on many websites, that to open one has to add those many strange things MS requests for Excel, using 'Type.Missing' helps to avoid setting all those parameters, at least for my test. '.Open(@"...")' alone as in dansmo's example did not work for me. I also had to use other commands to read/write in Excel. Again, I have Excel 2007 installed (and using to write in an xls document in prev. version 2003, so not an xlsx).

hlh

#17

4/7/2011 10:43 AM

Well, I also consider to write my code for more complex strategies in VS2010. I just tried it once, but it adds also quite some complexity and prep work - but at least I could debug my scripts. And I guess, if I could use .NET 3.5 on some occasion, I also could use LINQ which I find quite cool (not sure what for at the moment, but anyhow) and maybe code in VB.NET which I also like more than C#, but then again, using other people's example code would get more complicated.