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
Micro Economic Inputs
Share prices
Frequently
Energy prices & forecasts
Metals prices & forecasts
Deal Specific Inputs
Output forecasts
Conversion factors
Capex forecasts
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 tutorial 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
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)
1. Select Data - Import External Data - New Web Query (Excel 2003)
Select Data - From Web (Get External Data toolbar) (Excel 2007)
2. In the New Web Query address bar, copy the address of the webpage containing the data
3. Select the yellow box containing the black arrow next to the data you wish to import. It will turn green once selected.
4. Click Import
Step 3 - Select Query Location & Properties
1. 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.
2. Select Properties
3. Check - Overwrite existing cells with new data, clear unused cells.
4. Uncheck - Adjust column width
5. Give the table the name in the format Ext_x, where x starts at 1 and increases by 1 per table added.
6. Click OK and the query table will import the data
The imported data will look like this.
7. 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.
1. Insert the information in the following screen shot at the top of the sheet containing the query tables
2. Give cell L7 the range name time_stamp, and place the following formula in L8, =COUNTIF($F$10:$F$54,"Ext_*")
3. 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 tutorial.
These grey cells can now be linked into the rest of your model like normal inputs.
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