• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

Using C# in Excel

kean

Mathematics Student
Joined
5/31/06
Messages
246
Points
28
Folks,

Anyone has experience using C# in Excel? I would like to hear from you. Please feel free to discuss.

Thanks,
K
 
There is a thread out there that already discusses a little of it:

Integrate C++ program into VBA

Are you asking because this is something you have to do? Also, do you want to use C# as a replacement for VBA macros, or do you want to call the Excel object model from a C# program? Those are totally different issues.
 
C#

Joe, thanks. What I mean is integrated C# and Excel (not C++, it is C#).

Secondly, I want to know how call C# object model from Excel and vice versa.


There is a thread out there that already discusses a little of it:

Integrate C++ program into VBA - QuantNetwork - Financial Engineering Forum

Are you asking because this is something you have to do? Also, do you want to use C# as a replacement for VBA macros, or do you want to call the Excel object model from a C# program? Those are totally different issues.
 
..

More precise.."Calling the IMSL C# Numerical Library from Microsoft Excel"

Please let me know if you have any recommendation..or articles will do.:)

Joe, thanks. What I mean is integrated C# and Excel (not C++, it is C#).

Secondly, I want to know how call C# object model from Excel and vice versa.
 
Unfortunately, C# is not "integrated" into office, something you no doubt found out already. I believe the next version of office allows this, but even so that will not help everyone, there are many spreadsheets out using copious VBA code there that cannot be retired, so they will lurch onward zombie-like through the years, eating the brains of those unfortunate enough to have to maintain them.

But I digress.

Basically, VBA communicates with other platforms using an object model known as COM. It does not use .NET. So, you must either use 1) a "com-callable wrapper" technology to present C# objects to VBA as if they were COM objects or 2) a "runtime-callable wrapper" used to present the Excel object model to C# as if it were a .NET library.

If you want to control C# code from your excel sheet (which is probably what you want to do) you have to pick #1. If you want to control your excel sheet from C# code, you have to pick #2. If you pick #1, you will face wretched performance. If you pick #2 you will have to deal with Excel's wretched object model.

Be assured you will have to refer to msdn.microsoft.com quite a bit if you want to do something like this. The problem isn't figuring out how to do this - Microsoft's tools make it easy. The problem is figuring how to handle horrendous performance or debugging issues. Good luck.
 
VSTO

Joe, thanks again.

I think VSTO 2005 SE will do the job. Yes, a VBA wrapper for C# will do the job. I saw a person demonstrated Matlab integrated C#. I have contacted Microsoft and hopefully I can hear some good news soon.

I think MS announced earlier that the intention of VSTO is to "replace" current VBA platform. Due to Excel is using COM object model so porting to .Net may take some times. However, I do not see any tedious problems for MS.

I mean people who use VB 6 or VBA can still use their applications. For new development, the trend is going forward to .Net platform. As far as trading system platform I will vote for .Net.

Cheers,
K
 
it is not hard to use C# add-in in Excel under .NET framework; but C++.NET is not that easy to handle, i think.
 
I've had so much trouble with putting .NET on top of Excel, that I would not recommend it. For me it worked fine up to a point, but once I started doing a large amount of calculations/cell changes things would bog down and eventually would crash Excel. It got to be so much trouble that I gave up and ported it to be a stand alone VB .NET program. Things work great now, and it really didn't take that much more programming.
 
In VSTO 2008, it is not difficult at all. All built in and it is easy to use. All we need to do is to create shared assembly in VSTO and then check the add-in. You can easily share from Excel. There are at least two vendors offer C# QF tools. I couldn't remember the names. I will let you know if I find out.

These vendors provide easy solutions for metrics solution in C# and other stuff like graphical solutions.

I've had so much trouble with putting .NET on top of Excel, that I would not recommend it. For me it worked fine up to a point, but once I started doing a large amount of calculations/cell changes things would bog down and eventually would crash Excel. It got to be so much trouble that I gave up and ported it to be a stand alone VB .NET program. Things work great now, and it really didn't take that much more programming.
 
An update to the post above.
I have added the Euro option pricing (BS model) which I wrote as a stand alone C# app into Excel 2007 via VSTO. This involves creating a new ribbon that when you click will display/hide the panel where you calculate.

Here is a screenshot of the C# code inside Excel 2007 as an addin. It shows a custom ribbon as well as the input inside a task panel on the right.
 

Attachments

  • VSTO.JPG
    VSTO.JPG
    96.9 KB · Views: 669
Hi Andy

Great Job, Could you please give me more information on how to do that or Could you please post some sample code? Many many thanks!

raywin
 
Have fun. Keep in mind that I tested it with VS2008 Team edition and Excel 2007.

Looks nice Andy. I just want to add my opinion on Excel and .NET intergration (I know it might looks like an ad but I believe this information might be really usefull to visitors of this forum and could save them a lots hours of programming).

I've been working with Excel, VBA, .NET and C# for quite some time and for last few years in the as a developer in quantitative finance area (research / support). During this time I have been built several Excel <-> .NET frameworks, and very recently I finished a v.2.0 of my latest Excel&.NET RAD development kit and at the moment it is available as a public beta version.

The main idea - is to simplify Excel to .NET integration as possible - at the moment it is not a drag-n-drop type of integration yet, but quite close.

And it also comes with a lot of features: support of debugging, monitoring, profiling, document generation, remote assistance for the users, etc.

If you are interested - you may get a free fully functional beta version from here:
http://excel4net.com/Download.aspx

"Quick overview" page is here http://excel4net.com/Overview.aspx
and
"Getting started" documentation is here: http://excel4net.com/Documentation.aspx

If you have any questions about Excel4Net or a general question on Excel and .NET development - you can either post it here (if it's ok with Andy) or email me to beta@excel4net.com.

Happy Holidays!
 
Back
Top