Excel Automation

We will create a simple flow that uses an excel file, runs a task by looking at the inputs given and writes the results to the same excel file. Our example will take an excel file that contains multiple rows of stock symbols. The flow will take each stock symbol from excel and will get the stock price from google, then enter the price to Excel.

Create Flow

The following steps create a simple browser automation flow.

1. In Flow Designer click the 'Untitled' named button right to logo

2. Click New Project button in the popup window

3. Give a name to flow and press OK

4. Create an Excel file with the same content below, then save the file and place the named .xlsx somewhere in your computer. ( ex: stocks.xlsx )

5. Create the following flow by drag-and-dropping the nodes from the node palette, or by right clicking on the empty board. You can search the nodes by name.

• For more a readable flow, node names are changed here. You can find the name and the actual node from the list below:
  • Start ( Trigger -> Inject)
  • Open Stocks Excel ( Excel -> Open Excel )
  • Read All Symbols ( Excel -> Get Range )
  • Excel -> Set Active Cell
  • Browser -> Open Browser
  • Next Symbol ( Flow -> Label )
  • For Each Symbol ( Programming -> For Each )
  • Get Stock Price ( Flow -> Sub Flow )
  • Write Stock Price ( Excel -> Set Cell Value )
  • Excel -> Set Active Cell
  • Go To Next Symbol ( Flow -> Go To )
  • Excel -> Save Excel
  • Flow -> Stop

6. Double click the "Get Stock Price" node. This is the sub flow where we actually get the stock price from Google search. Create the following sub flow by drag-and-dropping the nodes from the node palette.

   

   Below is the list of nodes used in this sub flow:

   • Create Link ( Programming -> Function )
   • Go To Link ( Browser -> Open Link )
   • Get Stock Price ( Browser -> Get Value )

7. Go back to main flow by clicking "Simple Excel Automation" link from the breadcrumb

then click "Open Stocks Excel" node and change the Excel File Path property of the node to the full path of the excel file located on your computer. ( ex: /home/john/stocks.xlsx or C:\Users\John\stocks.xlsx )

8. Click "Read All Symbols" node and change the node's properties as follows:

9. Click "Set Active Cell" node below the "Read All Symbols" node and change the node's properties as follows:

10. Click "For Each Symbol" node and change the node's properties as follows:

11. Click "Write Stock Price" node and change the node's properties as follows:

12. Click "Set Active Cell" right next to "Write Stock Price" node and change the node's properties as follows:

13. Click "Go To Next Symbol" node and select the Label node's name to go to. In this case it is named "Next Symbol"

14. Double-click the "Get Stock Price" sub flow node and click the Edit action menu item on the "Create Link" named Function node,

then copy paste the below script and press Save.

var q = msg.stock.symbol + "+stock+price";
msg.link = "https://www.google.com/search?q=" + q;
return msg;

15. Click "Go to Link" node and change the node's properties as follows:

16. Click the "Get Stock Price" node and change the Selector parameter as follows:

Here is the copyable text version:

//div[@data-attrid="Price"]/span[1]/span/span[1]    

17. Click Save icon or press Ctrl+S for shortcut.

Run Flow

We have created our flow and now ready to run.

1. Press play icon at the toolbar.

2. We will select the desktop run in this document. If it's disabled it means that you don't have any connected robots. Click Desktop then select your robot (if you connected more than one robot) and press RUN.

note

Please make sure you installed a robot on your computer. You can follow this guide if you haven't. Or make sure you have connected your robot to your workspace. You can follow this guide if haven't.

3. When the flow finishes, check out the excel file located on your computer and see the updated stock prices.