is Matlab code a lot faster than Excel formulas?

52 views (last 30 days)
I have a very large Excel spreadsheet that I use to do an extensive calculation with many iterations. The calculation is done by spreadhseet formulas, and the iteration is done by a VBA macro.
The spreadsheet is 1000 columns X 10,000 rows, and one iteration takes about 30 secs. A typical calculation "run" has 30,000 iterations, so total calculation time is ~ 10 days
I'd like to speed this up by coding the spreadsheet in Matlab. I read it is up to 100 X faster than Excel, although to make it worth it to me, 10 X would be enough.
Given the above, is a 10 X speed increase a reasonable expectation when converting Excel formulas to Matlab code?
I'm a new Matlab user, and very grateful for your input. :-)
  6 Comments
John D'Errico
John D'Errico on 10 Jan 2023
While Jim is correct, in that code can be compiled to improve it, I'd point out that it may be missing an important fact. For example, suppose the excel solver is simply solving a large linear system of equations, using iterative methods. Great, except that is something MATLAB will do VERY well. And the code is already compiled. So you gain nothing by use of compiled code there. What really matters is the algorithmic improvement, of using a better method to solve the problem.
The issue is, we know nothing about what is being solved. Is this a problem that is amenable to the use of a better tool to solve an easily solved problem? Until then, there is no way to offer good advice.
Berliner JFK
Berliner JFK on 11 Jan 2023
Thank you for your answers. Very helpful and thought-provoking!
The spreadsheet is simple, just a lot of conditional equations and some basic math. It is so slow is because the amount of formulas calculated per iteration: 1000 X 10000 = 10 million formulas
Based on your responses, it sounds like what I want to do is "code" the spreadsheet so that it does exaclty what it's doing now, just a lot faster.
So ... given a relatively uncomplicated but huge Excel spreadsheet, could coding it in Matlab result in a 10 X speed increase?

Sign in to comment.

Accepted Answer

John D'Errico
John D'Errico on 11 Jan 2023
Edited: John D'Errico on 11 Jan 2023
A problem is that you have said it is a lot of conditional expressions. The thing is, MATLAB will be fast, IF you can use the capabilities of linear algebra to your advantage. It will be fast, if you can use vector and array operations. Then MATLAB can use tools like the BLAS to execute code blazingly fast. So if you were solving a huge linear system of equations, even with many thousands of unknowns, expect MATLAB to go like blazes. For something like that, I would expect to see a speed boost of thousands to 1, especially since you are using iterative methods to solve your problem.
The problem arises with branches, tests, etc. That forces MATLAB to be far less efficient. It prevents the efficient operations that make MATLAB fast. If your code is something in the middle, the time will be something in the middle too.
But what you are telling us is this is just going to be hand coded operations on thousands of cells, with many branches and tests. That will be laborious to code, as well as offering no real gain in speed.
Another place to gain would come from the use of automatic multi-threading. MATLAB can do this on many simple problems, so that all of the cores on your CPU can be used at once. However, that can never happen when you have branches. So if you are just adding each element between a pair of very long vectors together, this can be farmed out efficiently (and automatically) to multiple cores. Conditional operations prevent that from happening, so one core is all you will ever be able to use on highly conditional code.
Seriously, we still don't know enough about what you are doing to know if it will be faster to use MATLAB. I might expect some speed boost, but how much of a boost will depend on how much you can use vector and array operations. It would strongly depend on how well you can write that code using the capabilities of MATLAB. But if you just recode the computation from each spreadsheet cell into one line of MATLAB, you will probably be sorry. This is a mistake many people make when they migrate to MATLAB from a spreadsheet, in trying to treat MATLAB as if it is just a spreadsheet tool.
(I even considered trying to build a large iterative spreadsheet, just to compare the speed of the same code in MATLAB, but this would seem almost impossible to come up with a representative spreadsheet that MIGHT be comparable to what you would be doing. And then of course, I'd need to avoid using any tricks I know as a skilled user of MATLAB, that you might not know. Hmm. I might be able to do something though...)
  26 Comments
Berliner JFK
Berliner JFK on 19 Jan 2023
Thank you @dpb for your patience. I'm about to ask for more...
For now I will just do a 1:1 code, because I have to start somewhere. Consider it an experiment at my expense. The spreadsheet is huge. If that means that everybody loses interest, then I'll have to live with that.
BUT, I believe when you see the code and what it does, though, you'll recognize that it's "dummy level" simple. Like I've said from the beginning, just a lot of if / thens and and / or's, with some basic math. I don't believe there's an algorithm in the sense everyone else means.
In fact, this is probably a problem better suited for C++, but Matlab is worth the investment so LET'S HAVE FUN! :-)
So...
The clear goal is to end up with a fast program that performs ONLY and EXACTLY the intended function with no bells, no whistles, no extra matrices, no extra data. I understand and want that too.
I throw myself at your mercy. :-)
Berliner JFK
Berliner JFK on 13 Mar 2023
Edited: Berliner JFK on 13 Mar 2023
First of all, thanks again to everyone in this thread. Very helpful information, very appreciated!
A quick update on the project, in case you're interested:
  1. your points about not making a 1:1 translation from Excel -> Matlab are fully understood. I think the confusion was my assumption that I would have to adapt the way a spreadsheet does things to the way they can be done in code, so I never planned a "literal 1:1" translation.
  2. although this was not mentioned before, there is existing code that provides data which the spreadsheet uses, and it is written in C++. I'm not an experienced C++ coder (or experienced in coding at all), but I did write the code and can work in that environment. From what you all have said, C++ is very likely faster than Matlab.
  3. I've been taking time to modify the spreadsheet in preparation for converting it to code. Much of what has accumulated over time has been removed, and much of what is helpful has been kept and even fine-tuned. That's what I'm working on mainly now.
  4. For the moment, I think the wisest course of action, if speed is most important, is to make the (deeper) plunge into C++. It's not what I wanted to do, because I have a Matlab license available to me and a wonderful community to help, but it seems wise.
I know Matlab offers tremendous capabilities, but I'm probably not ready for that yet. My work is predictive in nature, so I will get there eventually. I've heard that "the LSTM algorithm is the best for predicting data on a time series".
So thanks and hopefully bump into you all again soon!

Sign in to comment.

More Answers (1)

Bruno Luong
Bruno Luong on 11 Jan 2023
Edited: Bruno Luong on 11 Jan 2023
Impossible to answer with such fuzzy question; the time depends on your hardware, your matlab version, your licences to acess to some toolbox suh as parallel computing, your data, your algorithm, your MATAB skill, ...
Here is the time of some arbitrary formula run on TMW server.
A=randn(1000,10000);
P = [1 2 3];
f = @(x) sin(polyval(P,x)).*(x>=0);
tic
f(A);
toc
Elapsed time is 0.137264 seconds.
  2 Comments
Berliner JFK
Berliner JFK on 11 Jan 2023
Thank you, Bruno.
If I understand you correctly, you're suggesting translating the above code into Excel formulas, and then comparing the speed in Excel with the speed in Matlab on the same machine?
Bruno Luong
Bruno Luong on 11 Jan 2023
I suggest you to test the same typical but possibly simple formula you would use on Excel and MATLAB to get your own idea.
I could be 100 faster, it could be the same speed, it could be slower. So far there is not enough details in your question that can be answered with certainty.

Sign in to comment.

Tags

Products


Release

R2022b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!