in this video we want to connect flowwise to Microsoft Excel using custom tools and zapier web hooks in the previous flowwise video we connected flowwise to Google Sheets using mac. com however with the introduction of python and Excel you might want to try this approach as well both implementation are quite similar so I recommend watching both tutorials and selecting the tools and services that best matches your needs and Tech stack in this tutorial we will ask chatgpt to set an alarm and add a row to our Excel table the data in the table can be processed further using python serving as a preprocess to another project part such an algo trading bot the logic behind the prompt and the further process is not subject of this video here we want to show the possibility of connecting flow wise to Excel and how to use Python directly in Excel cells for this we use Python editor which is like a slim version of Visual Studio code within Excel let's begin by launching Excel and creating a blank workbook we save this workbook in the cloud on our OneDrve I save it in a folder called zapier to have later one more step in the configuration of the zapier component we give our workbook a name like flowwise underscore webhook underscore alarm make sure to save it in the cloud we start by defining our columns in our case we have three columns the first one is thicker next is company and finally limit then we enter a test entry and adjust the column width having defined our columns and added a test row select these cells and create a table make sure to tick my table has headers when the table is created we give it a name like TBL underscore alarm for easier access later in zapier you can add another entry to be sure we Define the table correctly and it expands to include the row once set rename sheet 1 to alarm for further referencing in zapier with that we are ready to connect flow wise to excel if you are only interested in connecting flow wise to excel feel free to skip ahead using the timestamps however I suggest sticking around as the integration of python in Excel might inspire you to Learn Python to learn how to activate python in Excel and use the python editor in Excel check other videos on this channel if you have enabled python in Excel you can convert an Excel cell to a python cell using equal py followed by a Tab Key packages like pandas come pre-loaded you can quickly create a data frame from your Excel table by selecting it and toggle between Python objects and Excel values to retrieve the last entry in the data frame use ilock -1 which returns a series that can be converted back to excel values as long as the column structure remains unchanged the output size will remain consistent highlight this output with a green background you can reference values calculated by python within Excel for instance reference the latest limit value and display it in cell F6 this cell Position will not change and we can give it a yellow background we can do some formatting as we are used in Excel like centering and making bold and give a border we can easily get the row and column size of the pandas data frame with shape it returns a tuple when we show the Excel values we will get the row count and the column count the cells will be fixed in size and we can give them an orange background the next part is dynamic in size and can grow based on the data in the table here we want to count the alarm entries for each ticker so we create a data frame based on our table and use the group by function of pandas and group by ticker and count the entries this time we only highlight the headers and the rows will grow depending on the unique ticker values to see our python code in action we add another row and see how Excel and python work hand in hand and all of the values get updated we add another row and see the limit count for test increases now we add a new row with a new ticker as we have a new unique value for ticker pandas group y adds a new row showing the new ticker with count 1. to work easier with python and Excel we can use Excel Labs add in scroll down and select python editor python editor is like a simplified version of Visual Studio code within Excel with syntax highlighting intellisense and other IDE features python cells are listed and you can easily locate them some of the visual studio code shortcuts like Ctrl D to select next occurrence of the selection works here too and you can have multi-selection and change all occurrences at once in the formulas menu we have the python section and here you can show Diagnostics which acts like our console remember execution order matters and is from top to bottom and from left to right if you move cells around and disrupt the order you might encounter errors for instance if you move the cell defining DF you will receive an error from alarm sheet cell f8 in Diagnostics however restoring the original cell Position will resolve this issue the initialization section reveals a list of pre-loaded packages now we come to the part to connect flow wise to excel we use make.
com in the last tutorial to connect apps here we use zapier we can navigate to zapier and log to use a 14 days trial when logged in you see the dashboard here you can create a news app for the trigger we use webhook we can use the web hooks by zapier webhooks by zapier is a premium service for event we use catch hook which waits in our case for a new post request to the zapier URL we continue to get our webhook URL and click copy now we can switch to postman select post and paste the webhook URL for body we choose raw and Json and enter our data structure with some sample data and finally send it to the webhook URL back in zapier we test trigger and see the request and continue with the selected record for Action we choose Microsoft Excel for event we choose add row to table and continue I already connected zapier to my Office 365 account so I click continue for storage I use OneDrve for folder I choose zapier for spreadsheet I choose flowwise underscore webhook underscore alarm. xlsx for worksheet I choose alar and finally for table I choose TBL underscore alar and now we come to The Columns of the table here we assign the corresponding columns to the webhook parameters and the test entry one by one we choose the corresponding ticker and Company and limit to the values and click continue when everything is set up we click test step and see the result so this is functioning and we can publish and celebrate that we have automated one more thing when we check the Excel table indeed the record is added and the connection is working back in zap here we give it a name like flowwise webhook alarm and save now the connection is set up and we can proceed to flow wise to create a custom tool to call the webhook URL we've covered this step in previous flow wise videos so I explain it here briefly we go to Marketplace and select open AI agent then we click on use template to be able to edit the template under canvas first we remove the tools and add a custom tool by searching for custom and drag and drop it to the canvas and do some adjustments when we are happy with the positioning of the components on the canvas we can create a new Custom Tool we give the tool a name like set underscore stock underscore alarm and a description like a function to set an alarm for a stock if it reaches a limit next we add our properties we add ticker as string with the description the thicker symbol of the company and make it require next we add company as string with the description the name of the company and make it required to and finally we add limit as string to the description the limit for which we set an alarm and make it required to when all of the three properties are set we scroll down to JavaScript function to get a sample script we navigate to flowwise documentation and to use cases and web Hooks and scroll down till we get to JavaScript function and copy the script we paste it in Visual Studio code to have syntax highlighting next we update the webhook URL to the webhook URL we got from zapier and adjust the body to our data structure we copy the script and paste it into the JavaScript function box notice that we use the name of the properties proceeding with the dollar sign in the JavaScript function we add the tool and come back to the canvas here we add our openai key to chat open Ai and lower the temperature to be more deterministic next we connect the dots but before saving we choose a model which is optimized for function calling now we can save the flow and give it a name like flowwise Dash webhook Dash zapier and Save now we are ready to test our work we open the chat box and first we give a prompt which does not use any function calling like what is the thicker symbol of Netflix we get the answer nflx next we give a prompt that will use our Custom Tool like set an alarm when Netflix stock reaches 400. chatgpt uses the Custom Tool and send the information to zapierre webhook and zapier connects to Excel and adds the row to our table after the data is added to the Excel table the python script kicks in and updates other cells so we have the latest entry in the green box the latest limit in the yellow box the new table size in The Orange Box and finally our count per ticker updates and we have now a new row with NFL X as thicker and one as count now we test it with a new prompt set an alarm when Microsoft reaches 350.
