{"id":1560,"date":"2021-11-11T17:53:24","date_gmt":"2021-11-11T17:53:24","guid":{"rendered":"https:\/\/www.pyxll.com\/blog\/?p=1560"},"modified":"2021-11-11T18:04:19","modified_gmt":"2021-11-11T18:04:19","slug":"matrix-decomposition-in-excel","status":"publish","type":"post","link":"https:\/\/www.pyxll.com\/blog\/matrix-decomposition-in-excel\/","title":{"rendered":"Matrix Decomposition in Excel"},"content":{"rendered":"\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe loading=\"lazy\" class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/YJtpFVXv3v8?version=3&#038;rel=1&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_83 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-69ffaa5622f62\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-69ffaa5622f62\"  aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.pyxll.com\/blog\/matrix-decomposition-in-excel\/#Introduction\" >Introduction<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.pyxll.com\/blog\/matrix-decomposition-in-excel\/#Installing_the_required_dependencies\" >Installing the required dependencies<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.pyxll.com\/blog\/matrix-decomposition-in-excel\/#Writing_the_Python_function_%E2%80%9Clu_decomposition%E2%80%9C\" >Writing the Python function &#8220;lu_decomposition&#8220;<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.pyxll.com\/blog\/matrix-decomposition-in-excel\/#Getting_the_lu_decomposition_function_into_Excel\" >Getting the lu_decomposition function into Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.pyxll.com\/blog\/matrix-decomposition-in-excel\/#Calling_the_Python_function_from_Excel\" >Calling the Python function from Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.pyxll.com\/blog\/matrix-decomposition-in-excel\/#Conclusion\" >Conclusion<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.pyxll.com\/blog\/matrix-decomposition-in-excel\/#References\" >References<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\" id=\"h-introduction\"><span class=\"ez-toc-section\" id=\"Introduction\"><\/span>Introduction<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In this post we will learn how to decompose a matrix in Excel.<\/p>\n\n\n\n<p>LU decomposition is a useful tool in linear algebra. It decomposes a square matrix into lower and upper triangular matrices which are easier to work with than the original matrix. For a given matrix <em>M <\/em>using LU decomposition we can find a lower triangular matrix <em>L<\/em> and an upper triangular matrix <em>U<\/em> such that <em>L<\/em> multiplied by <em>U<\/em> equals <em>M<\/em>.\u00a0<\/p>\n\n\n\n<p>To write the LU decomposition function in VBA would be time consuming, potentially error prone, and slow to run. Python on the other hand has some fast libraries for linear algebra that we can take advantage of in Excel using the Excel Python add-in PyXLL.&nbsp;<\/p>\n\n\n\n<p>We will write the lu_decomposition function in Python using the <em>Scipy<\/em> package and then call that Python function from Excel using PyXLL.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Installing_the_required_dependencies\"><\/span><strong><strong>Installing the required dependencies<\/strong><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>We will use the Python package Scipy to do the LU decomposition. The Scipy package contains many advanced linear algebra functions that have been well tested and optimised for performance.<\/p>\n\n\n\n<p>If you are using Anaconda you may already have Scipy installed, but if you don&#8217;t you can install it by running &#8220;conda install scipy&#8221;.<\/p>\n\n\n\n<p>For other Python distributions you can install Scipy using the &#8220;pip&#8221; command, &#8220;pip install scipy&#8221;.<\/p>\n\n\n\n<p>In order to call our Python function Excel we will use the Excel add-in PyXLL. If you have not already installed this you can do so by following the instructions on the PyXLL website here <a href=\"https:\/\/www.pyxll.com\/docs\/userguide\/installation\/firsttime.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.pyxll.com\/docs\/userguide\/installation\/firsttime.html<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Writing_the_Python_function_%E2%80%9Clu_decomposition%E2%80%9C\"><\/span><strong>Writing the Python function &#8220;lu_decomposition<\/strong>&#8220;<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>We will first write a Python function called &#8220;lu_decomposition&#8221;, and later we will call that same function from Excel. Our function will take an input matrix as a 2d array of numbers and return a 2d array of numbers. This fits well with Excel since Excel displays numbers in a 2d grid.<\/p>\n\n\n\n<p>The LU decomposition returns two matrices, <em>L<\/em> and <em>U<\/em>. We want our function to return a single matrix so that the results can be displayed easily in Excel. So, we will add a second argument to our function to tell it which of the matrices we want to return. We can then call the function twice, once to get the upper matrix and a second time to get the lower matrix.<\/p>\n\n\n\n<p>To do the actual LU decomposition we use Scipy&#8217;s &#8220;lu&#8221; function. This is part of Scipy&#8217;s linear algebra package. You can find a lot more details on the Scipy website, and the docs for the linear algebra package can be found here <a href=\"https:\/\/docs.scipy.org\/doc\/scipy\/reference\/linalg.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/docs.scipy.org\/doc\/scipy\/reference\/linalg.html<\/a>.<\/p>\n\n\n\n<p>The Scipy function &#8220;lu&#8221; returns not only the two matrices <em>L<\/em> and <em>U<\/em> but also a thrid matrix <em>P<\/em>. This is what&#8217;s known as the permutation matrix. This is necessary to decompose improper matrices and performs row reordering necessary to decompose the matrix. The full result of the decomposition using this function is &#8220;<em>M = P L U<\/em>&#8220;. For properly ordered matrices the <em>P<\/em> matrix is the identity matrix.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nimport scipy.linalg as la\n\n\ndef lu_decomposition(input, result):\n    # Perform the matrix decomposition using scipy.linalg.lu\n\tP, L, U = la.lu(input)\n\n    # Return one of the results depending on which one we need\n    if result ==  &quot;P&quot;:\n\t\treturn P\n\tif result ==  &quot;L&quot;:\n\t\treturn L\n\telif result ==  &quot;U&quot;:\n\t\treturn U\n\n\traise ValueError(&quot;&#039;result&#039; is expected to be one of P, L or U&quot;)\n\n<\/pre><\/div>\n\n\n<p>We can now test this function to make sure it behaves as expected before we go on to calling it in Excel.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nimport numpy as np\n\n# Create an input 2d array (a square matrix)\narray = np.array(&#x5B;&#x5B;1,2,3],&#x5B;3,4,5],&#x5B;5,6,7]])\n\n# Call the function twice to get the L and the U matrices\nL = lu_decomposition(array,&quot;L&quot;)\nU = lu_decomposition(array,&quot;U&quot;)\n\n# Print the results\nprint(f&quot;L Matrix \\n {L}&quot;)\nprint(f&quot;U Matrix \\n {U}&quot;)\n<\/pre><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Getting_the_lu_decomposition_function_into_Excel\"><\/span><strong>Getting the lu_decomposition function into Excel<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Now that we know our Python function works we are ready to call it from Excel. To do this will will use the Excel add-in PyXLL.<\/p>\n\n\n\n<p>If you are new to PyXLL please visit <a href=\"https:\/\/www.pyxll.com\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.pyxll.com<\/a>. There you can find more information about PyXLL, including how to install and configure the add-in.<\/p>\n\n\n\n<p>We are going to use the <em>@xl_func<\/em> decorator from PyXLL. The xl_func decorator instructs the PyXLL add-in that this function is to be exposed to Excel as a worksheet function. PyXLL will export this function to Excel so we can call it from an Excel worksheet.<\/p>\n\n\n\n<p>The video <a href=\"https:\/\/www.pyxll.com\/docs\/videos\/worksheet-functions.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.pyxll.com\/docs\/videos\/worksheet-functions.html<\/a> is a good introduction to writing Excel worksheet functions in Python using PyXLL. If you have not written a worksheet function using PyXLL before this video is a good place to start.<\/p>\n\n\n\n<p>To expose our existing function Excel we simply import the <em>@xl_func <\/em>decorator from the <em>pyxll<\/em> package and apply it to our function. We want PyXLL to convert the values we pass it into a numpy array so we pass a <em>signature<\/em> string to the xl_func decorator telling it what types the arguments are expected to be, and what the return type is.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nimport scipy.linalg as la\n\n# Import the xl_func decoroator from PyXLL\nfrom pyxll import xl_func\n\n# And apply it to our function with a signature string\n@xl_func(&quot;numpy_array input, string result: numpy_array&quot;)\ndef lu_decomposition(input, result):\n    # Perform the matrix decomposition using scipy.linalg.lu\n\tP, L, U = la.lu(input)\n\n    # Return one of the results depending on which one we need\n    if result ==  &quot;P&quot;:\n\t\treturn P\n\tif result ==  &quot;L&quot;:\n\t\treturn L\n\telif result ==  &quot;U&quot;:\n\t\treturn U\n\n\traise ValueError(&quot;&#039;result&#039; is expected to be one of P, L or U&quot;)\n<\/pre><\/div>\n\n\n<p>You can find more about how PyXLL handles argument and return types in the user guide here <a href=\"https:\/\/www.pyxll.com\/docs\/userguide\/udfs\/argtypes.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.pyxll.com\/docs\/userguide\/udfs\/argtypes.html<\/a>.<\/p>\n\n\n\n<p>Finally we need to update the PyXLL config file, pyxll.cfg. In the config file there is a list of <em>modules <\/em>that PyXLL imports when Excel starts (and when PyXLL reloads). Add the module we&#8217;ve just written to that list of modules so that PyXLL will load it. You may also need to add the folder containing the Python module to the <em>pythonpath <\/em>in the same config file.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Calling_the_Python_function_from_Excel\"><\/span>Calling the Python function from Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Now we are ready to test this function in Excel. Once you have PyXLL installed and have updated your config, open Excel and the PyXLL add-in will load our Python module and the function will be available to call. If you had already started Excel, use PyXLL&#8217;s <em>reload<\/em> feature from the ribbon tab to reload the Python code.<\/p>\n\n\n\n<p>To call the function from Excel we enter \u201c=lu_decomposition(\u2026)\u201d and pass it a 2d array of numbers. The first argument to the lu_decomposition function is the square matrix we want to decompose, and the second argument is one of &#8220;P&#8221;, &#8220;L&#8221; or &#8220;U&#8221;, depending on which matrix we want to be returned.<\/p>\n\n\n\n<p><strong><img decoding=\"async\" width=\"624\" height=\"333\" src=\"https:\/\/lh5.googleusercontent.com\/kALxW_tO4VQwi8XkGGBs7ihYiHlgc3vs8FmaQOq_w1yrhLTEvHdNdIbTwH2vxbqgMfpJe9CzAgdTvDRdi-v8uMm3slC2hCOtQunI8BR06-a0iZG2PM8vXaAkZu3JCKGXXOrT2MTo\"><\/strong><\/p>\n\n\n\n<p>After obtaining the <em>P<\/em>, <em>L<\/em> and <em>U<\/em> matrices we can multiply then together and check the result is the same as our original input matrix. For properly ordered matrices the permutation matrix <em>P<\/em> will be the identity matrix.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Scipy contains many advances linear algebra routines that are not available to us using Excel alone. By using PyXLL we can make these complex routines available in Excel. Using a well tested and high performance library like Scipy can be a much better choice than trying to reinvent the wheel and implement complex routines in VBA, especially if you care about accuracy or performance!<\/p>\n\n\n\n<p>It&#8217;s not just Scipy that we can use from Excel. If you are already a Python developer you will no doubt already have your own prefered packages and your own library of existing code. You can call any Python code in Excel using PyXLL, making Excel a super-powerful front end to your Python tools.<\/p>\n\n\n\n<p>If you are not already a Python developer but are interested in how you can get more out of Excel by using Python, we have some resources to help you. You can find them at <a href=\"https:\/\/www.pyxll.com\/learn-python.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.pyxll.com\/learn-python.html<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"References\"><\/span>References<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li>Scipy: <a href=\"https:\/\/scipy.org\/\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/scipy.org\/<\/a><\/li><li>PyXLL: <a href=\"https:\/\/www.pyxll.com\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.pyxll.com<\/a><\/li><li>Python learning resources: <a href=\"https:\/\/www.pyxll.com\/learn-python.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.pyxll.com\/learn-python.html<\/a><\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Introduction In this post we will learn how to decompose a matrix in Excel. LU decomposition is a useful tool in linear algebra. It decomposes a square matrix into lower and upper triangular matrices which are easier to work with than the original matrix. For a given matrix M using LU decomposition we can find<\/p>\n","protected":false},"author":853,"featured_media":1562,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[12,2],"tags":[],"class_list":["post-1560","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-python","category-pyxll"],"acf":[],"jetpack_featured_media_url":"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2021\/11\/Untitled.png","jetpack_shortlink":"https:\/\/wp.me\/p7l3LP-pa","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1560","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/users\/853"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/comments?post=1560"}],"version-history":[{"count":1,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1560\/revisions"}],"predecessor-version":[{"id":1564,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1560\/revisions\/1564"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media\/1562"}],"wp:attachment":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media?parent=1560"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/categories?post=1560"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/tags?post=1560"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}