Tony! I finally got the chance to watch this. Very, very interesting stuff to keep in mind. Curious: minute 1:53 can you referenced the entire columns and have "spilled results"? Also, nice editing move with Fast Forwarding on minute 4:40 😁
Hi Carlos, haha hope you enjoyed it!! Yes absolutely, you can have dynamic arrays with PyXLL that return spilled results. Just return a list, DataFrame or other array type from Python and it will appear in Excel as a spilled array (see www.pyxll.com/docs/userguide/udfs/arrayfuncs.html). Of course for processing vectors of data that makes perfect sense, but for this test I wanted to see how it performed with lots of functions as lots of the real world sheets I see contain large numbers of different formulas and functions. Thanks for watching :)
I have not tried it yet but based on the documentation and videos like this, I am kind of amazed at how poorly architected the Microsoft offering is. Actually, let me elaborate on that a bit. When you see that some nice effort has been put into the UI (with the PY formula box; little green clocks for computation etc), it seems astonishing that the overall architecture seems so off. If one thinks that VBA is bad from a management and versioning point of view, then what is having the code as cells going to look like. What is going to happen when there is stateful code in the notebook that gets bitten by the execution/evaluation order?
Can you test Please... "VBA" v/s "PyXLL" v/s "MS Python in Excel", ......Which One is Fastest when the data have more than 10,000 Rows with more than 10 columns.... Also test which one is fastest.... "VBA Code" v/s "Using Excel Functions Using in VBA Code" say for example 1 : VBA Code For FindOut Unique Values v/s Excel Unique Function Used in VBA Code like that .....
Usually it's easier to write faster Python code than VBA, but of course it is possible to write slow Python code too! When it comes to Python in Excel, PyXLL is by far the fastest. I always recommend profiling your code before trying to make it faster. You might find this article interesting www.pyxll.com/blog/how-to-profile-python-code-in-excel/
Great overview, thanks!
Tony! I finally got the chance to watch this. Very, very interesting stuff to keep in mind. Curious: minute 1:53 can you referenced the entire columns and have "spilled results"? Also, nice editing move with Fast Forwarding on minute 4:40 😁
Hi Carlos, haha hope you enjoyed it!! Yes absolutely, you can have dynamic arrays with PyXLL that return spilled results. Just return a list, DataFrame or other array type from Python and it will appear in Excel as a spilled array (see www.pyxll.com/docs/userguide/udfs/arrayfuncs.html). Of course for processing vectors of data that makes perfect sense, but for this test I wanted to see how it performed with lots of functions as lots of the real world sheets I see contain large numbers of different formulas and functions. Thanks for watching :)
I have not tried it yet but based on the documentation and videos like this, I am kind of amazed at how poorly architected the Microsoft offering is. Actually, let me elaborate on that a bit. When you see that some nice effort has been put into the UI (with the PY formula box; little green clocks for computation etc), it seems astonishing that the overall architecture seems so off. If one thinks that VBA is bad from a management and versioning point of view, then what is having the code as cells going to look like. What is going to happen when there is stateful code in the notebook that gets bitten by the execution/evaluation order?
Can you test Please... "VBA" v/s "PyXLL" v/s "MS Python in Excel", ......Which One is Fastest when the data have more than 10,000 Rows with more than 10 columns....
Also test which one is fastest.... "VBA Code" v/s "Using Excel Functions Using in VBA Code" say for example 1 : VBA Code For FindOut Unique Values v/s Excel Unique Function Used in VBA Code
like that .....
Usually it's easier to write faster Python code than VBA, but of course it is possible to write slow Python code too! When it comes to Python in Excel, PyXLL is by far the fastest. I always recommend profiling your code before trying to make it faster. You might find this article interesting www.pyxll.com/blog/how-to-profile-python-code-in-excel/