Import live web forms into a Microsoft Excel spreadsheet

First locate the table you want to use from the web. We are going to use price information for the OEIC funds in Halifax ISAs (this is the table for ISAs taken out before 24 June 2010). In general, almost any table on a web page that shows data in rows and columns can be used. Note that some tables can look a bit messy when they are imported, depending on how they are formatted, but tables can be tidied up later using Excel’s standard formatting features.

Create a new blank workbook (press Control and N). In Excel 2007/2010, click the Data tab on the ribbon bar and click From Web in the Get External Data section. In older versions of Excel, click Data, then Import External Data, then New Web Query. In all cases, a miniature web browser will open (resize this if necessary by clicking and dragging any corner). Type the web address of the Halifax table (www.snipca.com/x2601) into the box labelled Address and click the Go button. Now click the Options button at the top right of the browser, select ‘Full HTML formatting’ and click OK.

In the mini-browser window you will see small yellow and black arrow icons down the left side of the web page. These identify tables that can be imported. Hover the mouse pointer over one of these arrows, and a blue frame will appear round the relevant table. Scroll down the web page until you see the arrow just to the left of the OEIC Funds column – click it. The arrow will turn into a green tick mark, indicating the table has been selected. You can select multiple tables on a page, but in this case we only need one. Next click the Import button.

A dialogue box appears asking where to put the data. Click the Properties button and tick the box labelled ‘Refresh data when opening the file’ in the Refresh Control section; this will update the table every time you open the spreadsheet. You can also make the data update periodically while the file is open by ticking the ‘Refresh every’ box and setting the minutes in the box next to it. When you’ve finished setting the options, click OK to close the dialogue box and then OK again to import the table.

The data table will appear in the spreadsheet (in Excel 2002/3, a small External Data toolbar also appears). To hide unwanted rows and columns, right-click on a row or column’s letter or number and choose Hide. To select multiple rows or columns hold the Control (Ctrl) key while selecting, then right-click one of the selected row numbers or column letters. In our example, we are hiding an empty column and three columns showing Yields. Note that deleting a cell, row or column won’t work, as each time the table is updated the deleted cells will be reinstated.