List all combinations in Excel

[Andy Nguyen]

Yeah, it rocks alright!
I'll tell you two reasons you should get 2007.

The total number of available columns in Excel 2007 increases from 256 (2^8) to 16k (2^14)
The total number of available rows in Excel 2007 increases from 64k (2^16) to 1M (2^20)
Excel 2007

We don't have 2003 at work, only XP and 2007 so all testing and production runs on 2007. As for the output, on the second spreadsheet I uploaded in this thread, there is a sheet called combolist that I use at the moment. It has the format we need for our model.

 

[charlesdwright]

Should I exclude the column "wrap" feature which starts new columns when 65,000 is reached, since you're running it in 2007?

 

[Andy Nguyen]

And replace it by the new limit of 1M rows since who knows if code runs into a loop.

 

[charlesdwright]

1. Changed output format.
2. Detects version and sets maximum row limit accordingly (assuming anything greater than version 11 has 1mm row capability).
3. row output is continuous without breaks for term combination changes.
4. diplays final total combination count.
5. I think that covers it -- oh, I didn't see any dupes, but if you do, pls let me know. there shouldn't be any.

I gained negligible processing speed by having the screen turned off, so I left it on.

See your pm's.

 

[doug reich]

1. Changed output format.
2. Detects version and sets maximum row limit accordingly (assuming anything greater than version 11 has 1mm row capability).
3. row output is continuous without breaks for term combination changes.
4. diplays final total combination count.
5. I think that covers it -- oh, I didn't see any dupes, but if you do, pls let me know. there shouldn't be any.

I gained negligible processing speed by having the screen turned off, so I left it on.

See your pm's.

Did you turn of automatic calculation (I don't recall seeing it when I looked at your code earlier)? That's a big penalty.

 

[charlesdwright]

Did you turn of automatic calculation (I don't recall seeing it when I looked at your code earlier)? That's a big penalty.

not explicitly in the code. I have it turned off on my app so I took it for granted. let me do that now...

Are you sure it would matter when there are no formulas in the workbook?

Well -- it had to be included anyway, because the user might not have it turned off, so thank you for calling my attention to it.

Attached...

Andy, please uncheck and delete all previous versions of chazCombine and install this one.

***
Mathematically, is it not true that for any term (i.e. 5y), the acceptable combinations form an upper-right triangular matrix? And therefore all acceptable pair combinations (i.e. 5y,7y) are contained in the multiplication of two such matrixes?

 

[doug reich]

Are you sure it would matter when there are no formulas in the workbook?

Note sure, but I can see how it might, depending on how excel works. If it was "dumb", then it would look through all the cells in your sheet (or at least those that could potentially have formulas), which would be very slow just by virtue of having a lot of cells to look at.

Also, I see a performance penalty on my system just by having a market data add-on, and it helps in that case.

 

[charlesdwright]

Also, I see a performance penalty on my system just by having a market data add-on, and it helps in that case.

Do you mean an RTD?

 

[doug reich]

RTD or DDE
​

 

[charlesdwright]

RTD or DDE​

I understand them to be very demanding. I can't recall whether they push or pull, however.

 

[Andy Nguyen]

Chas,
Your new code seems to work fine but i'm trying to nail down the logic and a formula that generate the total number of pair so that I can know in advance how many pairs it will generate.
This should be simple but the math can be easy to do wrong.

 

[charlesdwright]

Chas,
Your new code seems to work fine but i'm trying to nail down the logic and a formula that generate the total number of pair so that I can know in advance how many pairs it will generate.
This should be simple but the math can be easy to do wrong.

I like Doug's idea of labelling the elements as he did. Once you have the math worked out, I think a pairs class would be useful. Do you think the matrix approach mentioned below is worth pursuing?

***
[edit]

Indeed, I believe it is. I think I have this problem solved in the general case. I'll try to pretty up an explanation and attach it later. I think the magic number is 648,675 pairs.

***
[edit]

Pls. see attached and let me know if there are any errors.

 

[charlesdwright]

Andy,

Pls. review the attached and verify that the pairs are valid.

cdw

 

[Andy Nguyen]

Chas,
It looks correct.
The number of pair within a term should be n(n-1)/2.

 

[charlesdwright]

Chas,
It looks correct.
The number of pair within a term should be n(n-1)/2.

if, by term, you mean a combination (i.e. 5yr 5yr), that is correct.

If you mean (5yr, 7yr) -- the normal case -- it is n(n+1)/2 (because 5(0,1)7(0,1) is a valid pair).

the number of valid elements in a series (i.e. term combinations for 5yr -- 5(0,1), 5(1,2)...5(29,30)) is n-1.

 

[Andy Nguyen]

Correct about the cross term number of pairs.
Finally, the total number I should see is 3n(n-1)/2+3n(n+1)/2=3n^2
Agree?

 

[charlesdwright]

Correct about the cross term number of pairs.
Finally, the total number I should see is 3n(n-1)/2+3n(n+1)/2=3n^2
Agree?

Agree. I don't have time to relearn Latex so I've attached doc with a more formal statement.

Tweaking the code now.

***
[edit]

sorry, I guess I don't agree. If n refers to the number of terms in each series, i.e. (0.0,0.1...0.30), then it would be 3(n-1)^2.

I take full blame since I used "n" to represent two distinct totals, and then changed it "s" at one point... the example should say it more clearly than I have.

see example attached, with typo corrected.

 

[charlesdwright]

voila

Andy,

Attached is chazCombine 1.5, new and improved.

The rules have changed a little: instead of selecting the data without that first header row -- the one that contains the series titles (5y, 7y, etc), please select ALL rows in the data table, including the header.

Give it a shot and let me know how you like it.

p.s. Assuming it works as advertised, this is going to cost you a recommendation on my Linked-In profile.

 

[charlesdwright]

Set notation

This might be overkill, but in order to eliminate ambiguous communication with projects like these, I attempted to describe Andy's with Set Notation.

There's probably more than one way to skin the cat, but I've given it a shot. Feel free to offer constructive criticism.

I suppose I could offer proofs and explore the claim in Note 1 when as and if time permits.

 

[doug reich]

Speaking of ver

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

Definition 1. Let T = {0, 0.1, ... } be the set of generic terms. There are 31 terms.

Defintion 2: ..

Theorem 1. (|P_s| = |T|-1) In our case, (|P_s| = 30)

That way, there isn't any looking up and down the page. You might also use footnotes for your notes, but let's not go overboard

(If you post the tex source, I would be willing to reformat it this way.)

2. I'm a little confused by your notation in some places. In number 3, what does it mean that "\(s \in S\) such that \(s = \{ t: t \in T\}\)". From your explanations at the bottom, perhaps you mean \( S_T = \{ \{ s, t \} : s \in S; t \in T \}\)?

In #6, why is the \(n = \{ 1, .. \}\) inside the cases? Should that be below? I think if it was in paragraph form, that would help.

3. Isn't \( \frac{1}{2} \frac{|S|!}{|S-2|!} = \large( \begin{array}{c} |S| \\ 2 \end{array} \right)\) ? (You can use "\(\binom{}{}\)" or "\choose{}{}" I believe in regular latex).

4. You should consider using the \mid command instead of | when writing "such that"s for your sets: it puts space around the pipe and is more readable. Other definitions might benefit from "\ " to make the formulas more readable.