MATLAB Integration with Excel - MATLAB
Video Player is loading.
Current Time 0:00
Duration 50:26
Loaded: 0.33%
Stream Type LIVE
Remaining Time 50:26
 
1x
  • Chapters
  • descriptions off, selected
  • en (Main), selected
    Video length is 50:26

    MATLAB Integration with Excel

    Using MATLAB® integration with Excel®, learn how to:

    • Access the power of MATLAB from Excel with MATLAB calculations that run in the cloud (production or development)
    • Manipulate Excel spreadsheets from MATLAB
    • Load data from and write data to Excel either interactively (automated code generation) or programmatically
    • Automate any of the processes listed above

    Published: 20 Jul 2021

    Hello, and welcome to recession integration with Excel. When we talk about interaction between MATLAB and Excel, we need to clarify what we need. You see the data is sitting in Excel and we need to read it from it, or do we want to create an Excel report writing some MATLAB output in it. Or perhaps we MATLAB developers and we use Excel as our front end. And from there we want to call MATLAB functions.

    We could even launch a report for an Excel being a MATLAB program in charge of automatically generating it. Or we may be MATLAB developers, and we'd like all the people to run our MATLAB algorithms through an Excel. Without needing a MATLAB license and not even know that those functions are not Excel.

    Of course, the speed will be the same as when we work within MATLAB. And when we are developers of algorithms and we work with the MATLAB. Because at the end of the day, it's MATLAB behind it. When we think about, we want to read data which is in Excel we could do it interactively, or we could do it programmatically.

    When we do it interactively, we're going to see an example in a minute. We'll see we have an interface like where we see in the screen, and we just point and click and then MATLAB will generate the code itself. So that way we avoid having two program. The whole thing means that you can get to automate your processes, and eventually they won't need you to be there only when you are planning it. And then MATLAB generates the code and automate everything.

    If you don't want to use these interfaces and you want to do it yourself, you can do it programmatically and use these functions. Every table could be very appropriate in many cases, it will allow you to-- well in all the cases really you could use that. It will allow you to read data which is not homogeneous in the sense of you can have numeric data, You can have text data et cetera.

    If you have only numeric data, you can go ahead and use writematrix. Both instructions will have different options, but they get used in a very similar way. That is when we read data. But what happens if you want to write data from MATLAB into Excel? Then you can use instructions like writetable and writematrix, so with the same logic.

    When we think about where our data is, we can see here that could be just a file, that we have in our computer for instance. So file location, we just present what it is and the name, or we could think that file is sitting in the cloud. It doesn't matter to MATLAB, we still go ahead and set the instruction. So we'd say, readtable, the file location, whether the location is a local location or as you see here, somewhere in the cloud. So you use it the same way whatever it is.

    Now we're going to go and see an example in action. So we go into MATLAB. We have here several Excel files, we downloaded this from our website. To do that, we use the instruction we're safe, and just put the URL and you get all the files downloaded into the location you specify.

    So let's pick up one of those, and right click to open to see what's inside. So you can see the format of these file, being here a month begin today et cetera. These are the columns and then every observation it is a row.

    So how can we read this into MATLAB? Because when we want to do any analysis, the first thing we want to do is to read the data. So I'll double click here, we're going to do as I said before, in an interactive way. You can see says, opening around six files. So it is a pretty big file, and that's why is detecting it.

    OK so once you've got it open here, there are certain options, you can adjust. You can say read everything as a table or as independent column. So if everything was numeric et cetera. You can specify your range as well.

    And here some unimportable says you can say, replace them with known. So once you have the data is ready to operate on it, specifying whatever it is known or is not known. Ignore it, or interpolate on that issue, it will be your decision.

    So here I could say import they-- I'm going to specify the name of the variable that you want to read with that name. So import data-- I don't know why he's doing that. OK, so it's important data, let's go and have a look. Here it is, if I double click, there you go. So you're already can work with these variable.

    The other thing you could do here, is you can say generate function, and MATLAB is going to generate a function that will read these files for you. You don't have to go and write yourself the readtable instruction. So all these things that you see here, these rows are for robustness.

    All the algorithms in MATLAB, are very robust in the sense of, if you create an import file which is a function that will read from that file, what happens if you call it without providing a file name for instance? It could break. So but that always avoids this. So this is all the error checking that you can see here.

    Different options that we'll be discussing in a minute. And then at the end you have the readtable instruction. So if you realize you don't have to program anything, MATLAB has done for you, which is called cogeneration and that leads you to automate all your actions. Then I could save these, save as, I could enter the same name import file.

    So once I have saved that, I could just come to MATLAB. And let's say, the next month I have to read an Excel file, I can use that function and call the new name, whatever it is for the that month. And XLX, and it would read it. So I would just say, data new or whatever and he will read the new data. OK, so that's how you automate the processes.

    OK so when we think about reading the data for an Excel into MATLAB. How about if we say, we want to write instead. So let's see I have here my data, which I could have obtained from different algorithms or whatever, and now I want to write.

    I can just use the instruction read/write table, specify the variable, and just any name that you want for the Excel that you want to create. Let's say, a write this, keep in mind is quite big data. So it'll take a little while. You can see MATLAB is still busy.

    So we are in this directory, as soon as it finishes, we'll see how the new file has been created. OK while is doing this, let's go to the recommendation. I want to show you how when you read, what options you have to specify. It has finished already. So we'll see the options that our table provides.

    If we have a look at these directory, I can see I have here my file, so it has created it. Which if I open it, I can see is the same as the one before. Let it open, because his big, there you go. OK, so he has the same format month, begin day, whatever. But now is not a CSV is an XLX, so we can see nicely, nicely formatted.

    So I'll close these. So we were going to go into the documentation, to see the different options. If I search for readtable instruction. I can see your first-- by default you can just provide the name of the file, or you could also have different options-- name, value.

    And it gives you the different choices, like the file type. Whether certain values, you can treat as if they're missing. So you will ignore them in your analysis. How you want to deal with the imported text , et cetera, et cetera.

    So this would be one way. Or a different way is to use what we call, detect import options. Which is a different way of providing options for viewing. Here it is. So I can call this function and provide a different options.

    As you can see in this example, when the input to detect import options-- when MATLAB detects that is an Excel file, automatically, you see here it says a spreadsheet import option. So you realizes that type of the file, and it goes on and this with the as an spreadsheet import options. I could directly search that, because what we're reading from is from an Excel.

    This happens because you could apply detect import options to all the type of files. But in our case, we're talking about Excel. So we'll go here, and now we go to the next sample. Select the options I call this instruction, and then I set up in this object, I set up the column that I want, only the last name, the available type is going to be categorical.

    And this preview is the same as if I was reading this file, but only a few lines. So it doesn't matter whether it is, it was a huge spreadsheet, is only one I read a few lines. So as you can see, when I read this spreadsheet specifying the options that I have determined with the instruction, then is going to do what these options say. So it's going to read only the last name.

    And it is going to be a categorical type column, because it's that limited number of surnames, and there is nothing between one and the other, it's not a continuous range. So this is how you can use the options. You define it once and then, whenever you use the it will apply that to any spreadsheet.

    One other option that you can use directly reading every table, is when you call with table and specify format auto as a name value pair. What you say in there is that, you specifically say not to follow the specifications given in detect import options. But let MATLAB just judge the type of data arriving in each column.

    OK another case that we may think is, what happens if we want to read a particular column? So let's go in here, and I have an a spreadsheet called bond data. It has this tap, and this tap. So what I'm going to specify is I want to read this sheet writing data, and clarifying that the first row is header line. So how do I say that?

    I come here and say, detect import options. So the sheet writing data number header lines 1. So I'll just run these. And then I'm going to specify, that the column that I want to read is only the column writing.

    And finally, I called with a new variable, and say readtable. So I'm going to read these a spreadsheet with these options. If I go to this variable, you can see it has only read this column. In MATLAB documentation you can find little tips. And one of the tips you will find for these readtable is that the extension XLXS is slow.

    So Microsoft advises you to use XLSB type. So if you notice that it's a bit slow, if you use the other type you'll go faster. So we've been seeing different options, I only show you a few readtable with name, value detect import options reading a given column. And we could think as well how we can read several sheets.

    So in MATLAB documentation when we went here to readtable, you saw certain examples. And some examples are going to refer to a spreadsheet called airlinesmall. So let me go and search for another function which is the one where you want to be using when, instead of talking about one specific spreadsheet, I want to talk about several spreadsheets or several taps dealt with all together.

    And this instruction is going to be spreadsheet data store. Again, you could only use data store and let MATLAB know that we're talking about a spreadsheet. OK so you can search this and find different examples.

    This is the one I was referring to. So here, if you run this instruction by the way, the help is interactive. So you can just select it, and then right click and evaluate selection. So you would just run those instructions in their workspace.

    So I can just pick up these, I can just copy and paste it as well. So what I have here is a data store. And what is a data store? Is a pointer to allocation. So I haven't read anything, I'm only saying be ready, I'm going to be reading you, this could be a pointer to many spreadsheets, like the one I saw at the beginning where we have many CSVs.

    So you just put a pointer to that location, and that's all. We haven't read anything yet. For the spreadsheets that you don't know where they are, because they come with MATLAB documentation. You could just go and say, which and the name of the spreadsheet airline whatever, and I'll tell you what it is, you will see is in your installation of MATLAB.

    OK so let's say I want to open this spreadsheet from MATLAB, I just go and call it like that, there it is. So here you can see that I have one year, another year, another year, many years, they're all different sheets. What happens if I want to read them all one after the other? MATLAB will treat them as a congratulation. Concatenate the second one after the first one, the third one after a second one, so it deals with them in one lot.

    So let's say the data store I will just go and say, sheets, and I would say equal empty. If you're looking at documentation, that means all of them. Otherwise, you will have to specify 1, 2 or 1, 2, 3 or whatever. But in this case, I want them all.

    So now I can write my own variable, you can choose any variable, and I just call readds. When I say this at this moment, I am reading the data. So now the data is in the workspace. I could only do this if the data fits into memory. If you had huge data set that cannot fit, then MATLAB knows how to deal with it, using tall arrays.

    So what happened if I want to specify read all the sheets but only one column out of all the sheets? Then, I will reset my data store, so it gets ready to start reading again. And then they go and say, ds, and I just specify which column. OK so if I now say, my output variable what I'm going to be reading, and I read that data store. Oops I didn't put a semicolon.

    So then you realize, we have them here. So here and even show you the output, 1996, and then if I carry on further down, you see the next year, and the next year. So as you can see MATLAB chose them putting one after the other, which makes it very easy because it is going to process them all in the same way.

    OK the last thing I would mention to you regarding reading and writing to MATLAB. Let me come here. It would be-- we've been writing with writetable or writematrix et cetera. What happens if, if you want it not only just put the content and that's it.

    But what happened if you want to customize the format and say, write in these cell, and putting that green or light blue or whatever? So you can customize it as much as you like. But instead of using writetable, you could use a act, x server, which is an instruction that will allow you to be the master of Excel, and then you can just play with it at the very cell level. You can insert more information in the MATLAB documentation.

    This is just the data store I was talking before. OK so now let's see how we can call MATLAB for an Excel. There are two ways, where we call it spreadsheet link and Excel addings. The spreadsheet link, it is like a window, you look through the window and you can see everything that is in MATLAB.

    Not only what comes with the tools that you get from MathWorks but also any programs that you have made any dot and/or any .mlx. It's just a window which the goal is you can have whatever you want in Excel as your front end and from there, you call MATLAB exactly the same as you would call any Excel function. For that obviously, you need the MATLAB license, because you are programming with MATLAB.

    The second option is what we call the accelerants. And what it is you do the algorithms in MATLAB, and you can give it to all the people or the colleagues, customers, whatever, and they can run the same algorithms without having MATLAB. So for this we're going to see different options. And one of them could be going to what we call MATLAB Production Server, and the other one with the MATLAB add-ins. We'll deal with them in a minute.

    Let's start first with the spreadsheet link. So for that, I'm going to go to-- I'll close this, and close that, start clean. Spreadsheet OK so let's see-- I have here whatever you want to have, tables with data or you use our day-to-day work. And you want to use some of that data that you have here as input parameters to a function.

    You can use one input parameter, you can use many. In this case, to make things simple in my demo, I'm going to just put one parameter called n, and the size-- the value it's going to be 5. As I say you could put as many as you like. OK so how these works. You see I have these icon here, this means I've got a spreadsheet link installed, I'll tell you in a minute how you can install that.

    I've got it installed, and then I'm going to use it. The first step is, I start MATLAB. Was going to do is going to start MATLAB session, and it's going to link that MATLAB session with this spreadsheet. And from that moment, whenever I do things in Excel and sends data to MATLAB, it would be to that MATLAB session.

    So spreadsheet link works in three ways-- with three steps. Step 1, I send data from Excel to MATLAB. Step 2, I ran a MATLAB instruction exactly the same as I would run it in MATLAB, but in this case, I'm going to run from Excel.

    Obviously, the function will give me an output. So step 3, would pick up the output, which is in MATLAB workspace and bring it back to Excel. Which means, front Excel when I have my input data and just run in MATLAB functions, which give me an output. And I don't even realize I've gone to MATLAB.

    I can do this in a manual way, semi manual and automatic. Let's do, manually first. I select these cell and say, send data to MATLAB. With the name I can call it n, my variable called n. Then if I want to check just for you to know, I come here, and they can say-- sorry I forgot to clear the content before, and I want to clear it, so we don't get confused.

    So I'll do it again, send the data to MATLAB in the variable n. I come here-- I'm sorry I was in the other MATLAB. One second it's going to see. So n is here, and it has the value of 5. So I've done this step number 1.

    Step number 2, ran a MATLAB command. And I want to say, I'm going to call a function call MyMagic-- let me explain this function. I've got a function called MyMagic, and all he does is to call magic function. Magic is in MATLAB, and I created this little function with it-- MyMagic picks up the input n and calls magicn gives me this output set, that's all.

    So I could call my magic from here, but instead, I'm going to call it from here. Set equal called MyMagic, and because I know I have n in my workspace. Let's hear what's happened, set is here. So he has done it. This is the output.

    And step number 3, is I want to pick up this output back through an Excel. So I position myself where I want to have output. Let's say here, and say get data from MATLAB, and specify set, which is the variable we output-- an optical. So this has been very manual.

    I could do it same e-manual with the following instruction. I could say, MATLAB eval string, and here I say my data, my data, well, the output is going to be, let's put set that, and my data is in m, the input data. So what I want to run now is the same. But I put a different output variable to compare.

    So whenever you use a 0, it means it has done improperly. I come back here, and I can see I have the variable there. So that was when it in a semi-manual way, because I don't have to go to here and say run command. If I got it programmed in my cell, this instruction, all I have to do when in there is just enter, or you re-evaluate all of this spreadsheet, then it will evaluate this cell. And finally the most automatic way would be to come to this example.

    I'm going to close this one, these examples, so right click, Open. OK. So you can see the difference here is I have this button. So this button has the instructions that I mentioned before, the ML eval string, within ABI cell-based macro. So that means I don't have to be doing it manually. I would act the same way. Start MATLAB to associate a MATLAB with this spreadsheet. Takes a bit to open up.

    It's opening now. OK. OK, it's completely open now. So I'm going to click here. And you can see it has run. It has run, and checks up sentence. So it is calculating. You can see also that MATLAB is a lot faster than Excel, in this case 97%. Every time you run, it's a bit different, but it's a lot faster anyway.

    OK, so this is how you run it automatically. And if we want to see how we program this here, you can see, one second, we find MATLAB instructions. There it is. OK, so from here we would start MATLAB, so we don't have to go and start MATLAB. We can just start in a programmatic way. And then we say put matrix.

    So what I'm saying here is put the content of the cell C2 into the variable n-sames in MATLAB workspace, equally the content of C3 in the variable n-steps in the workspace, and so on. And then eventually I'll get to a valid string, where here it's calling for the value of a variable relative. OK, so output variable, MATLAB MLX, and then the input.

    So this could have been exactly the same as our MyMagic example. And the last thing I want to show you before we go to add in is, let me close this, because it was linked to the spreadsheet. OK, I was showing you before how you could call MyMagic from here. One thing that I didn't show you and I want to do now is how I call the function, with a break point.

    So I'll go to my function. We opened another MATLAB, and we didn't send the n here. So we need to send it back again, and position my cell here, send data. We're using this name. And now I make sure I have a break point here, with the left button of my mouse. And if I now run the command, set it call MyMagic, it will take me there, stop step. So I advanced one step.

    And now I can see set has been produced and I can see the value. If this function was a lot bigger, it would be a lot more useful, to be able to get back and stop, in the different variables and see the value. So I just say continue. And you can see how MATLAB has created the set. So it has executed that value correctly.

    So this was the same as before, but we put in breakpoints, and stop it in the middle. Let's close this. So we go back to MATLAB. OK, we've been using this example with Excel, to see how to communicate between Excel and MATLAB. I showed you before, when I opened my spreadsheet, that I had this icon.

    And I told you that, I will show you how to make the icon appear for those of you who haven't installed a spreadsheet link yet. So once you have a spreadsheet link toolbox, all you need to do is to come here, file options, add-ins, go, and you see how I have this clicked. For those of you who don't have it, all you have to do is browse, and search here for the directory, where you have your MATLAB installed.

    So you would just select this, say select the right file, and click OK. Now I'm going to show you how to find this directory where MATLAB is installed, in case you don't know it. But that's all you have to do, OK? And suddenly the icon will appear. This directory would be the following.

    You're right here, MATLAB root, and it tells you what it is. So you come here, you find toolbox, say link, and you would select this file. OK. So now we're going to move on. The second way that we mentioned that how to call Excel, sorry, MATLAB from Excel. One way was with a spreadsheet link and a MATLAB license, and the second way was without MATLAB license.

    This case would apply when you want to share your algorithms with people who don't have MATLAB. The idea here is we have our algorithms, MATLAB algorithms, .m, mlx, and we'll be using our MATLAB Compiler products to convert those MATLAB files into something else. It could be, a .exe, it could be CVLL, it could be Java.net, et cetera. In our case, for Excel, which is what we care in this talk, we're going to concentrate in the fact that we can convert those MATLAB files into Excel add-ins, or into .ctf files.

    And we'll see in a minute how we're going to use that. One will be imported directly by the user. I clear my Excel add-in, give it to my colleague, he imports it into his own Excel. And he can use it from there on. And the other way is that CTF. Instead of giving it to somebody who needs to import into Excel, I will just leave that right to IT, who will be placing it somewhere.

    And this option is a lot more centralized, because they place that in a central location, and anybody can call them from their spreadsheet, without having to bring it to their own Excel file. Here we have created a function, MyMagic.mlx. What about if we want to give it to other colleagues, who want to run it as if he was unaware of the Excel functions?

    Those colleagues won't even realize that it is a MATLAB function. In this case, the MLX is very simple, but it could be a very complex function. And you can call any function that you call from within MATLAB and at the same speed. You could even connect to external sources and create reports, exactly the same as you do from MATLAB, because it is MATLAB at the heart of these.

    OK, and we just said that we convert our MATLAB algorithm into an Excel add-in or a CTF file. But how do we do that? Let's go and have a look. I go to apps and I search for Production Server compiler. This is what I'm going to be using to produce that CTF file. And in this case, I am going to choose this one, because that CTF file can be called from Excel or from some other software.

    So I'll choose this option. And then what I would do here is I will pick up MyMagic, which is the file I want to convert, and click here, OK. I'm not going to do it because I already have done it before. But basically, once I click OK, then I would click package here. And when it finishes, I would obtain the following result.

    This file will be produced, sorry, this folder will be produced. And within this folder, I have this other one. What this file is, the .exe, I will be using this, I will double click, and with that, the XLA, well, sorry, this one first. I will have to give it to the person, my colleagues who are going to be calling my MATLAB algorithm without having MATLAB.

    So I give it to them, they double click on it, and then they get an XLA install in their Excel. So basically what you do is they have a thin client into their Excel. We go to the folder. And here we have the CTF.

    So we have to think on the client, the user, the colleague who is not going to have MATLAB, on the client side, which is himself on his spreadsheet, he will have the thin client installed. And then this CTF, he will give it to IT. So IT will place it, this part, would correspond to IT. IT would place it in a directory, with all the other CTFs from different functions. One can value a delivery, the other one can calculate it a value at risk, et cetera.

    And here is the person who wants to call that function from his spreadsheet. So this is the client and here is the server. And how do we call that function through an Excel? We would first need to have that MPS running. But for the sake of us developers of the algorithm, for the sake of us being able to test the algorithm, that it calls properly, and it works properly, there is a little MPS functionality within our compiler toolboxes.

    And what we need to do now is to have that software running. For that, what we're going to do is, we'll go to the project. This was the project that we opened, when we went to the apps and selected a MATLAB production server, and then this option, and this is where we hit bucket.

    So what we do here is we hit test client. And then we say start. What we are saying with this is MATLAB production server is running and is waiting to be called. It can be called for an Excel from somewhere, or it can be called from office software, any software capable of EC and HTTP request. So several colleagues could be calling this in a concurring way, no problem.

    So we now go to Excel. And since we have MyMagic ready and waiting to be called, let's call it. And here is the result of calling MyMagic. I could equally have called this with a break point. So I could put the break point, call it again, and there it is. I step through it.

    And I have this set, continue, and I got the result. I can now stop it. You can see here, tells you when the server was called, whether it lists incorrectly, whether everything finished successfully. So here is a log of what was happening in the server, because we're pretending to be the MPS. MPS stands for MATLAB production server.

    So we have spoken about how to convert one algorithm into a CTF. But what happens if we wanted to convert a lot of them? One hundred, or however many, we wouldn't want to go into the app, you know, the way we came here, the app, and then we search for here, and here we added our function, and so on.

    What we would like to do is to be able to do it like in a mass execution, all in one go. A little trick that we can find here is this was the result of my compilation. So here is the log file. And what it tells me is the instruction when I hit package, to produce that CTF, you can see here MCC with some parameters.

    So you can basically copy them, and instead of doing it through the app, you can directly give the instruction within the MATLAB command window. So this is the way how you could put this one, which is for one file, and another 20, or another 100 for every other file that you want to compile. So basically you could just, sorry, I would copy this. I would copy this and create a file. You can create here mass compiling, and here you can copy this MCC, and another MCC for another file, and so on.

    Then you just have to run this .m, and all of them will run, and you will get the different CTFs in one go. OK, so we have seen how you can call from Excel, you can call a MATLAB algorithm that has been transformed into a CTF, and then it can be called from different spreadsheets, from different people, different locations, and they all call exactly the same version of a file. So it's all very centralized and it doesn't give any error, it's always the latest version there, and so on.

    Another alternative I mentioned before would be you take your algorithm, your .mlx or .m. You convert it with the MATLAB compiler into an Excel add-in. And that Excel add-in gets put into the Excel of the person who wants to call the function. So when we come here, I would give, if I am the person creating the MLX, I give it to the person who doesn't have MATLAB but wants to run the algorithm. And the person would come here, developer Visual Basic, and it would just come and import that BAS file. Once it does that, that would be MyMagic would be available for the person to call it, in exactly the same way as I call it with MATLAB production server.

    So it would just come here and write MyMagic in exactly the same way. But the difference is that he has his own add-in in his own spreadsheet. So every time that function gets modified, the person needs to make sure that he brings the new BAS or the new XLA file into the spreadsheet. It works as well. But the other way is more centralized, and it's always the latest version that you call. So it's not room for that kind of error.

    With this, I covered everything I had for today. So as a brief summary, we've seen how to read data through an Excel into MATLAB and how to write into Excel, also how to access, well, within that reading and writing, we've mentioned the different options available, within grid table, on the right table, right matrix, et cetera. We've also seen how to call from Excel, how to call MATLAB, to write a spreadsheet link and Excel add-in, and we've gone into the details and characteristic of each of those options.

    So I want to thank you for your attention. And if you have any questions or any points you would like to discuss, please feel free to contact anybody at MathWorks. Here are my contact details, and thank you very much.

    View more related videos