This section describes how to use the ipull and ipullDisplay in-cell functions to pull live updating data into your Excel spreadsheets from ipushPull.
iPushPull lets you pull data into Excel in several ways. The ipull and ipullDisplay in-cell functions are especially suited to fast updating data (e.g. financial information) as they are very efficient and add little overhead to your spreadsheet calculations.
Note that the easiest way to insert an ipull is to use the Insert Pull button on the ipushPull Excel ribbon toolbar - see this video for details.
Pulling live data into Excel is a two stage process. Stage 1 pulls the data into Excel, stage 2 displays it on the sheet.
Stage 1: ipull
First you have to insert an ipull function. Here's the syntax:
=ipull(PageName, FolderName, Interval, Update)
- PageName - the name of the page you wish to pull into your spreadsheet
- FolderName - the name of the folder the page belongs to
- Interval - OPTIONAL the minimum number of seconds between updates. If you do not set this it defaults to the page's standard interval setting
- Update - OPTIONAL turns the page pull on or off. TRUE or FALSE. Default is TRUE.
|Pull values at default interval||=ipull("my_page", "my_folder")|
|Pull values every minute||=ipull("my_page", "my_folder",60)|
|Pause Pulls||=ipull("my_page", "my_folder",,FALSE)|
When you enter an ipull into your spreadsheet it should look something like this:
It's usually more convenient to type the page name, folder name etc. in their own cells and use cell references in the function:
If your pull has worked the function will display a success message. Note that the message includes the dimensions of the page being pulled. This means that the page is being pulled successfully. The timestamp will update each time new data is received.
Stage 2: ipullDisplay
To actually display the live updating data on your sheet you use the ipullDisplay function. Here's the syntax:
- ipullCellRef - the reference of the cell where the ipull is being called from
|Display the page being pulled into cell A4 using ipull||=ipullDisplay(A4)|
When you enter an ipullDisplay into your spreadsheet it should look something like this:
If you enter the function like this it will only display the top left cell of the data page. To display the entire page you have to use ipullDisplay as an Excel Array Formula. Array formulas let you display the results of a request across multiple cells. They are slightly fiddly to use but very powerful.
To enter an ipullDisplay function that displays the entire page you need to first highlight a range of cells with the same (or larger) dimensions as the page. You can see the number of rows and columns in the message returned by ipull.
Using your mouse, select the top left cell of the range then, keeping the left mouse button pressed down, drag down and to the right until you've selected a big enough range (Excel will display the size of the range you've currently selected in the Name Box in the top left corner). It doesn't matter if the range you select is bigger than the page contents.
When you are happy with your selection, release the mouse button and start typing your ipullDisplay command.
When you've done this, press CTRL+SHIFT+ENTER at the same time. The ipullDisplay function will now be copied into every cell in the range you just selected, and the whole page will be displayed:
Note that ipullDisplay only pulls back the values from the page - it does not pull styling. See this page to see how to do this.
For tips on creating, deleting and resizing Array Formulas, see our Array Formula Hints and Tips page.