Excel has some powerful features that allow the user to directly query databases and websites. Whilst the application of such functionality is not going to be used on a daily basis amongst the financial modelling community, there are a few instances where it can save time and reduce errors, which is something we all should embrace.
Most financial models have various ‘levels’ of inputs, ranging from the macro-economic inputs, to the deal specific inputs. The table below provides some examples of inputs for an M&A mining transaction.
| Macro Economic Inputs | Update Frequency |
| Inflation factors | Infrequently |
| Currency rates & forecasts | Reasonably frequently |
| Bond rates | Reasonably frequently |
| Micro Economic Inputs | Update Frequency |
| Share prices | Frequently |
| Energy prices & forecasts | Frequently |
| Metals prices & forecasts | Frequently |
| Deal Specific Inputs | Update Frequency |
| Output forecasts | Reasonably frequently |
| Conversion factors | Infrequently |
| Capex forecasts | Reasonably frequently |
Where inputs have to be updated frequently and can be sourced from a website, Excel’s web query functionality can provide a robust solution, that is easy to develop and implement.
This article will;
- Demonstrate how to set up web queries in Excel
- How to control multiple queries and their updating
- How to process the results into you model
Download the Excel file accompanying this article.
Step 1 – Locate The Data Sources For The Inputs
In this example I’m going to import Australian Bond data. The following page on Bloomberg.com contains the data required – http://www.bloomberg.com/markets/rates/australia.html
Step 2 – Create the Web Query in Excel (Excel 2003)
- Select Data – Import External Data – New Web Query (Excel 2003)
Select Data – From Web (Get External Data toolbar) (Excel 2007) - In the New Web Query address bar, copy the address of the webpage containing the data
- Select the yellow box containing the black arrow next to the data you wish to import. It will turn green once selected.
- Click Import
Step 3 – Select Query Location & Properties
- Select where on the Existing Worksheet you want to place the query table. Select one cell which will be the top left of the table.
- Select Properties
- Check – Overwrite existing cells with new data, clear unused cells.
- Uncheck – Adjust column width
- Give the table the name in the format Ext_x, where x starts at 1 and increases by 1 per table added.
- Click OK and the query table will import the data
The imported data will look like this.
- Insert the titles and data in cells D37:F40. This provides usual information about the data that has just been imported.
Step 4 – Control the Updating of the Tables
Once the query tables have been created it is important that the updating of them is controlled ie The user chooses when the tables are updated, and the date and time of any update is displayed.
- Insert the information in the following screen shot at the top of the sheet containing the query tables
- Give cell L7 the range name time_stamp, and place the following formula in L8, =COUNTIF($F$10:$F$54,”Ext_*”)
- Insert the following code into a visual basic module in your workbook and link the routine to a button at the top of the worksheet (view – toolbars – forms – button)
The code above updates all of the query tables in the worksheet (External Data) and then places a time and date stamp at the top of the sheet when the update process completes.
Step 5 – Linking the relevant data into the model
By this stage all the data that is required, plus a lot more besides, has been imported into your model. All that remains is to select only the data fields that are required and then feed them into your model. It is best practice to collate all these inputs into one section before using them in your calculations. There are a few methods that can be used to locate the data within the query tables. These methods can be seen in the excel file accompanying this article.
These grey cells can now be linked into the rest of your model like normal inputs.
A completed excel file containing this and other web queries examples can be downloaded from: http://www.digitadvisory.com/knowledge/tutorials/Linking-Excel-to-Web-Data.aspx
Notes
This process can also be applied to query tables that link to access databases. Some data fields may be text formatted or contain currency signs, so some processing may be required. Using functions such as Mid, Left, Replace etc will allow the values to be isolated
Hej John,
I have been seeing this done before for share prices and wondered how it was done. Now I know thanks to you. Is there a way of making the Excel link always reloading when the online data changes? This has some risj but I wonder anway.
RLG
Rebecca,
Using the method above it is not possible to have Excel automatically update if the underlying data changes. In this example the data is ‘pulled’ into Excel, rather than being ‘pushed’. However it is possible to achieve something similar by using one of the following;
To update the data every time the workbook is opened, replace the first line of the code above, Sub Update_Queries(), with Private Sub Workbook_Open() and copy the code into ThisWorkbook.
Or to udate every time the External Data worksheet is selected, replace the first line of the code with Private Sub Worksheet_Activate() and copy the code into the sheet object. In the download example it is the External Data sheet.
If you did want to continuously update the data in the background use a Do / Loop statement. This will continuously run the macro and Application.Wait(Now + TimeValue(“0:00:10″)) will pause the macro for 10 seconds before running it again. I would not recommend this approach as it would use processing power on your PC, which may slow down other tasks. Also, the share price data that is available for free is delayed, so even with continuous updating the information will be 15 – 30 minutes old.
Regards,
John
What is qt? im using Excel 2007 and its not working
if you are interested in this, there is a useful Yahoo Group called the Stock Market Functions Add-in. It is an add in that allows you to pull in specific data from web pages. Mostly aimed at the US due to the availability of investmetn data, it can be used for UK and other general data.
See
http://finance.dir.groups.yahoo.com/group/smf_addin/?v=1&t=directory&ch=web&pub=groups&sec=dir&slk=1
There are also other functions that can bring stock prices and basic share information into excel similar to DDE links.