Skip to main content

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

Untitled Project

  1. Click New Project button in the popup window

New Project

  1. Give a name to flow and press OK

New Project Name

  1. 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 )

Excel File

  1. 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.

Flow

  • 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
  1. 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.

Sub Flow

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 )
  1. Go back to main flow by clicking "Simple Excel Automation" link from the breadcrumb

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 )

Excel File Path

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

Read All Symbols Options

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

Set Active Cell Options

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

For Each Symbol Options

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

Write Stock Price Options

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

Set Active Cell Options

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

Go To Next Symbol

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

Get Stock Price

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;
  1. Click "Go to Link" node and change the node's properties as follows:

Link

Go to Link Options

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

Get Stock Price

Here is the copyable text version:

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

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

Save

Run Flow

We have created our flow and now ready to run.

  1. Press play icon at the toolbar.

Play Icon

  1. 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.

Robot Choice

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.

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

Result Excel