List all combinations in Excel

Well, I asked for it, didn't I?;)

Actually it's not TEX, it's a .doc with MS Equation. I'll attach it if you'd like to horse with it. In your #3, "S" must be designated as |S|.

In my #6, I think the logic is muddy.

Thank you for the feedback.
 

Attachments

Sorry I meant |S| not S in #3 -- I was just talking about a more intuitive description of the number of terms.

I knew it didn't quite look like Latex (that and you said you weren't going to use latex...)
 
doug reich said:
Speaking of ver

1. You might make it clearer to use paragraph form for each of your items using Definitions and Theorems.
Click to expand...

Seeing how word does the equations, I can see why you would do it as you did: it's kind of a pain.

It would be quick for me to turn it into latex: is it worth the effort?
 
doug reich said:
Seeing how word does the equations, I can see why you would do it as you did: it's kind of a pain.

It would be quick for me to turn it into latex: is it worth the effort?
Click to expand...

Well, if you mean, "am I paying for it," I'd say, probably not. ;D

No, not worth the effort, imho. I did it as an exercise to clarify and express the logic, and to learn something new about set notation.

Seriously, thank you for your input on it.
 
Chas,
I tried the new version and it seems to produce the correct list. Now, in practice, we are not going to
select the table before we run the code. It will have to be written in vba to do this automatically.
What I'm going to do is to put the pair data in a table and have vba call it using list objects.
The user can extend or modify the table as desired and our vba code should just populate the result in another Excel table.
By table, I mean table as a list object.
It's also a nice side project to code this in C++/C# and reuse it later all.

Thanks for the great help. I'd be glad to give you any LinkedIn recommendation you need ;)
 
That should be an easy enough tweak. I presumed a higher degree of user interaction. At least, with the number of valid pairs cut drastically down, Cxx isn't as critical. It's not like you get to take a nap when running it now.

Bumping up the number of series is something I've been thinking about, in case that need arises.

I thoroughly enjoyed solving the quantity problem and doing the work. I hope you find it useful.

Also, Doug's representation of the pairs lent some inspiration to my approach to solving for the number of valid pairs. It got me toying with the idea of having the output in the same notation and as a matrix, but time has grown too tight so that's another idea on the back burner.
 
Andy said:
Chas,
You seem to spend quite some time with Excel/VBA. Have you ever used it with more modern tools like .NET via VSTO or other way?
I ask because as much as I like Excel, it is not designed to run heavy computation. So if you work with Excel, that question would arise naturally?
The original question was asked here Trading models: from Excel/VBA to C++/C# - QuantNetwork - Financial Engineering Forum
Click to expand...

Short answer is, yes, I developed an auto execution rig for the Trading Olympiad in .NET; it's not a bad learning curve and it felt very much like a cross between VBA and C++. But that's all I've had need of so far; I've never worked with VSTO. [thank you for the link].

In 9814 there was some heavy number crunching so I did that in C++ and called it as a .dll from Excel/VBA. The reason is that the VBA user interface is so ridiculously easy to craft, that it frees you up to work on the fun things like algorithms, and leaves you looking sharp because it's so slick.

So the C++ as .dll thing was like the best of both worlds: lots of muscle and easy to use.

I think Excel is the most incredible thing going, allowing me to solve problems so quickly, focusing more on the problem and less on the finer points of coding. I am constantly amazed at how useful it is. I think of it as a workbench to prototype solutions on. If I were a carpenter, it would be my favorite hammer; no, it would be my workshop.

How about you, do you work with other environments much?
 
Chas,
C++ DLL is something I mean to do for a while. In my aps, there are plenty of number crunching functions written in VBA that will gain a speed boost if I replace it with a dll.
I tend to work more with C# in a .NET environment. There are arguments about C# < C++ in term of performance but it's something I can live with in return for many snazzy features of .NET like the tons of components I can use for my window form applications.

By the way, I did write a tutorial on how to write a C# DLL. Do you have a good link that I can use to quickly get a C++/dll to work?
Most of the links/tutorial I tried are crappy.
 
Andy, I can't vouch for this http://www.developerfusion.co.uk/show/1973/2/.

If you PM me an email address, I can forward some emails I exchanged with Muting about this a while ago, which I believe you will find useful if you follow them. And if perchance that effort ended up in another of your tutorials, I think we'd all better off. Especially if you posted it!

[edit]

Since you're running 2007, you might find this interesting: call Excel functions from DLL: http://msdn.microsoft.com/en-us/library/bb687851.aspx
 
Chas,
Just happened to notice that the when I modify the code to take from a table and generate it in another table, there is a big slow down when the workbook is 2007 format (xlsm). I thought it was the code so I went and checked.
In any case, when I tried to save the workbook in xls extension, the code generates the table in a second or so.
Do you happen to know why the list object operations are much slower in 2007 than in earlier version?

For reference, I include the example.xls. Just run the vba code inside and it will fill the table.
 

Attachments

Andy,

I ran what you sent me. I clocked the processing time:

start 9:45:14 AM
end 9:45:14 AM


But then again, I run 2003.

***
Because I don't use 2007, I googled around a little bit. It is an issue.

Bad Request

VERY SLOW EXCEL 2007

There are some suggestions in these, one of which I'm sure you won't like. ;D
 
Chas,
Just found a bug. Not sure why I didn't see it earlier.
When you start the 5Y vs 7Y combo, the first loops run correctly but the second loop for the Y-coordinates should reset from beginning.
Code: 
0.00%   3.00%   0.00%   3.00%   5Y   7y 
0.00%   3.00%   3.00%   7.00%   5Y   7y       
0.00%   3.00%   7.00%   10.00%   5Y   7y       
0.00%   3.00%   10.00%   15.00%   5Y   7y       
0.00%   3.00%   15.00%   30.00%   5Y   7y       
3.00%   7.00%   3.00%   7.00%   5Y   7y
...
The last line should be
3.00% 7.00% 0.00% 3.00% 5Y 7y

With this, the number of total combination should be (3n^2+3n(n-1)/2)
 
I'll check on this, but it might be a few days before I can get to it. I'm not sure it's bug, per se, as I think I designed it this way. Is it for all cross terms, or just for the 5/7?
 
Andy said:
It happens to all cross term pairs.
I'll fix it tomorrow since I modified some of the original code.
Click to expand...

It's not a "bug" when the user changes the specs, Champ. ;)

I still have to dig out my matrixes and see what you've got going on, just so I can feel like I'm on top of it.

~c
 
Ok, it's not a bug. It's a feature ;)
Will have to look over the spec sheet of all the pair you posted a while ago.
This is getting better every day, Chas.
 
Andy said:
Ok, it's not a bug. It's a feature ;)
Will have to look over the spec sheet of all the pair you posted a while ago.
This is getting better every day, Chas.
Click to expand...

The giveaway on the design is that I lived a little dangerously and dimensioned the array that holds the combinations by a computation of the theoretical count before the process. It's a double-check on the logic of the theoretical count and of the process.

When actual count matched it exactly, I sort of patted myself on the back.

SO, if you don't modify the code that dimensions the array to comply with the new theoretical count, you'll get an error.

You could deal with the array differently, but I think you're too much of a professional to take the easy way out.

~c
 
Chas,
It actually was my fault who said everything looks correct when you posted the doc in post #33. Now I look at it again and all the pair in the 5 vs 7 table should be eligible. We should have 25 pairs in that table.
Somehow, I have a feeling that if I rewrite this in C#/C++, the code will be much leaner. We'll see.
 
Andy said:
Chas,
It actually was my fault who said everything looks correct when you posted the doc in post #33. Now I look at it again and all the pair in the 5 vs 7 table should be eligible. We should have 25 pairs in that table.
Somehow, I have a feeling that if I rewrite this in C#/C++, the code will be much leaner. We'll see.
Click to expand...

I would expect nothing but the leanest code possible from you, Andy.

In my own projects, if it's new material, my first pass purposely contains lots of explicit logic, in order to make the refinements easier to understand until the process is settled. Once it is, then I look to lean it up where performance is critical (a handy use for DLL's).

But, as long as loops are being incorporated, I'm not sure how much leaner the actual matching algorithm could be. That I'd be interested in hearing about.

But I'm always thinking about the day I have to go back into it and say to myself, "what on earth IS this?"

I would like to help you with the C++, but I'm tied up for a couple of days.

How would you do it, if not as a dll?
 
You must log in or register to reply here.

Similar threads

N
Excel interop The server threw an exception. (Exception from HRESULT: 0x80010105 (charting in excel)
Replies
11
Views
22K
Evancroby
E
C
Student t copula in Excel
Replies
3
Views
12K
Andy Nguyen
Andy Nguyen
Andy Nguyen
How to create UDF in Excel using C# and Visual Studio 2008
3 4 5
Replies
83
Views
101K
tabbaab
tabbaab
Back
Top Bottom