I need more calculation speed when I have formulas in 5 columns and 1,000,000 rows

[ReneNeyrey]

Hi All. I have a big problem, or better put, a big excel spreadsheet problem. I download an excel file containing all stock options. It's a .csv with 1.35 million rows, one row for each stock option. Of course, I divide it into 2 different sheets. Of the 30 columns of data already provided on each row, I delete all columns except the 8 columns I need. I enter 5 columns of formulas, the same formulas for each row. My formulas are to find pairs of stock options that meet certain conditions which make them profitable. They are all IF formulas.
The problem is it takes excel too long to calculate. Even after it finishes calculating, if I make 1 small change of any kind, it takes forever to calculate. Even saving takes forever.
When I work out the kinks, I want to do this everyday, because options can change dramatically in 1 day and present new profitable option pairs that quickly.
I'm running a Dell Inspiron 17 7000 with Eight Core Intel Core i7-8550U CPU at 1.80GHz. And 16 Gig of ram. The CPU usage always goes to 100%, and stays there, while excel calculates. I have closed all other running programs and even turn of WiFi in hopes of more speed.
If the only solution is more CPU and Memory, what can I get that is the fastest at calculating excel spreadsheets? Someone told me the only way to get top speed is with a desktop, not laptop. Which is fine with me, whatever works.

Thanks to everybody who reads this,
Ray J. Johnson, Jr.

[MrShorty]

Unless your laptop is really old, I don't know that I would expect great improvements from relatively minor hardware improvements. Usually when I see these questions, the real improvements are made by making the formulas more efficient. You haven't shared any of your formulas, so we cannot make specific improvements. Some common improvements:
1) Avoid linear exact match lookups like a plague. Use much more efficient binary or similar lookup algorithms. This part may include sorting the data or other adjustments.
2) Identify any calculations that will never change once calculated and copy/paste as values
3) Identify intermediate calculations that are duplicated by every copy of the formulas and move those calculations into helper cells/rows/columns.

There's probably not much along those lines we can do to help without seeing a sample of your data and formulas, but those are the kinds of things I would be looking at.

[horseguards]

Hello Ray,

First you need a live feed that will deliver all the prices to your laptop. The prices/data can run, stored and subject to formulas and caculations in Excel via dde's. The choice, here, is between real-time (for when you're ready to trade) and delayed, which is much cheaper. I would suggest looking at what the exchanges are offering: CBOE, CME, CBOT (assuming you're interest is in US options).

I think you may be looking at too great a range of data. What fields are you looking at, and what are you calculating via your formulas? The priority for options trading is implied volatility and the greeks. You cannot trade options with static data - you need up to the second prices (real-time) because option premiums change whenever the underlying changes.

Maybe you could consider index options - only a few to consider and heavily traded, hence, great liquidity.

[hrlngrv]

There may be ways to improve the performance of matching different records, but without YOU providing any details about what that involves, it's impossible for anyone else to provide a meaningful answer.

If you can't provide details, they you're on your own, and good luck.

[ReneNeyrey]

Thanks everyone. In order of replies: I am using a lookup on 1Mill. rows, so I can get rid of that. I am using copy/paste as values and saving as .CSV, which help. Next, it's my formulas I need to simplify They are multiple "IF" copied down the 1M rows, in about 4 columns.
horseguards: I'm using CBOE and get last nights data in the morning. It takes them 15 minutes to have my download ready. I'm not using implied volatility or greeks. I'm locating pricing irregulates which provide arbitrages. If you Google "Put Call Disparity" what I'm doing is explained. These disparities are needles in haystacks, but they're golden. The only sure way to find them is to look at all of them. I've traded options for 40 years, including index options. "Put Call Disparity" almost never happens in Index Options, but I check them anyway.
hrlngrv: I don't remember saying I can't provide details. This forum doesn't permit attachments by newbees. In a sec, I'll try to attach a small spreadsheeet to see if am allowed.
Again, Thanks to everyone who had a positive response. It's a lot better than being on my own.

[ReneNeyrey]

I attached a spreadsheet to see if it works.

[ReneNeyrey]

Is this something you can open and see formulas?
Note: Columns B thru I are downloaded from the CBOE I keep, all other columns they download I delete, they are useless for this purpose.
I realize I should not repeat the same info in columns A and P so I get rid of 1 of those columns.
What's important about this spreadsheet is the 23 rows I included out of the 1.3M rows I started with. in the DIVERGENCE column those "-4.26"ish are the big numbers (the needle in the haystack) I'm looking for. This frequently means there is an Option Arbitrage and therefore, profits.
I guess it is the "IF" formulas in 5 of my 6 calculation columns that I need to simplify.
Thanks again.

[ReneNeyrey]

I can't tell if my attachments are there. Would some one tell me yea or nay on there being an attachment?

[ReneNeyrey]

OK. I see the attachments now.

[Pete_UK]

Yes, there is an xlsx file attached to Post #6, as well as a PDF file attached to Post #8 (not sure why you can't see them).

Regarding the xlsx file, you can make these improvements:

K2: =MAX(AVERAGE(F2:G2),M2)

M2: =IF(E2="C",MAX(L2-D2,0),MAX(D2-L2,0))

You can often reduce IFs using MAX (or MIN) if you are only dealing with numbers. Many of your other formulae refer to text values, so can't easily be changed.

I'm not sure why you need to reproduce the value from column H in column L with the formula:

=+H2

It is only used in column M, and you could just as easily refer to H2 rather than L2 in that formula.

Hope this helps.

Pete

[FDibbins]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[ReneNeyrey]

Per your request, I have changed the thread name. I hope.

[AliGW]

You need to update the OPENING POST, please. You have updated post #12.

[hrlngrv]

FWIW, the original post's title provided all the information I needed to figure out the OP needed to improve either recalculation or macro execution speed.

There comes a point at which fastidiousness becomes counterproductive if not outright hostile.

[ReneNeyrey]

Thank you, Pete. I wish I had known to use MAX a long time ago. I did what you said in columns K and M, and I changed column C text ("C" and "P") to Unicode numbers in column N, to get away from using text. I got rid of the repetitive column H. The reason I had duplicated column "DIVERGENCE" in column A was to use lookup formulas which seem to need the lookup number to be in the left-most column. DIVERGENCE is the result I'm calculating, then I did a lookup to rank the top 20,000 results to find the best DIVERGENCE. That's was not shown in the spreadsheet I up uploaded. I suppose that maybe once the DIVERGENCE is calculated I could save it as a .CSV and then sort by DIVERGENCE, high to low.
Thank you, AliGW, I see what you mean.

[AliGW]

Thank you for updating the title.

@hrlngrv - if you wish to complain about the moderation here, the correct way to do it is by private messaging an administrator. Thank you.

[FDibbins]

FWIW, the original post's title provided all the information I needed to figure out the OP needed to improve either recalculation or macro execution speed.

There comes a point at which fastidiousness becomes counterproductive if not outright hostile.

You may not feel my moderation was warranted, but the title did not clearly explain what the problem was. Rules are applied to everyone equally - obviously some members will not agree with some rules but that doesnt mean they dont have to abide by them, that's life, you cant please all the people all the time.

[Pete_UK]

I did what you said in columns K and M, and I changed column C text ("C" and "P") to Unicode numbers in column N, to get away from using text

There is no great advantage in doing this, so you could change the formula in O2 back to this:

=IF(E2="C",K3,"")

and do away with column N. In P2 you could change the formula to this:

=IF(OR(O2="",SUM(K2,O2)=0),"",D2-H2-O2+K2)

though I'm not sure that this would make any great difference in speed.

Did the other changes improve anything?

You can use an INDEX/MATCH combination rather than VLOOKUP, and so you wouldn't have to duplicate anything in column A, but there are no examples in your file so I can't advise further on this.

Hope this helps.

Pete

[ReneNeyrey]

Hi Pete. YES! I did a download last night, again, 1Mill rows, then added the new formulas. Unbelievable improvement! I did not use any lookup formulas. Then saves as .CSV. To find the best DIVERGENCES, I'm doing some sorting which is nearly as good. Thank you again, Pete, you have been a huge help. Your P2 formula looks a little simpler than mine, so I'll try it. I'll try and post what I'm looking for that INDEX/MATCH might work for.
Thanks again, Pete, AliGW, and everyone else who contributed to solutions.

[Pete_UK]

Glad to help.

If you think that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete