Tip #2001-12: wTable Simplifies the Process of Performing What-If Analysis on Financial Information
What are the advantages of using wTable to design real-time financial models?
The way you get real-time data into an Excel worksheet is through Dynamic Data Exchange (DDE). The DDE
protocol was created by Microsoft to permit the transmission of messages and data from one application to
another through the Windows operating system. The DDE protocol is modeled on the client server analogy.
The server application can deliver the data to the requesting client application. All the real-time data
vendors have server application which may be separate or an integral part of their main data retrieval
application. The requesting client application is any application which supports DDE - Microsoft Excel
in most cases.
One of the serious shortcomings of Microsoft's DDE protocol is its inability to support dynamic cell
referencing. The DDE protocol forces you to enter the instrument code and exact field name in order to
create a real-time, automatically updating link. You are not able to reference the cell with the instrument
code or the cell with the field. If you want to change instrument codes or fields you need to edit each of
the cells containing DDE formulas. If you have a large complex spreadsheet, making changes is a difficult,
tedious and error-prone process. Performing what-if analysis becomes a chore.
As an example, suppose you designed an Excel worksheet model to analyze a group of instruments in a specific
industry group such as the pharmaceuticals. In your model you used pricing and analytical information from
a data vendor. Interspersed in the cells containing the DDE formulas, you added cells with your own specific
calculations. Suppose you want to perform the same analysis on another industry group. If your DDE retrieval
function supported cell referencing, all you would need to do would be to change the value in the cell
containing the instrument code. All the dependent cells would change to display the new real-time and
calculated information. Performing what-if analysis would be greatly simplified.
Willows Solutions has created wTable, a new way of accessing real-time data in Microsoft Excel. It allows you
to use cell referencing so whenever you change the instrument codes or fields in your spreadsheet models,
wTable automatically detects your changes and updates your real-time formulas to match. wTable is not specific
to a single data vendor. It lets you retrieve real-time information from any data vendor which supports DDE.
It even allows you to translate all real-time formulas from one data vendor to a backup data vendor.
Read a more detailed explanantion of how wTable works.
Financial Ratio Comparison Model
We have created an Excel workbook model which lets you compare how a group of equities measures up against
various financial benchmarks such as the Price to Book Value, Price to Cash Flow (EBITA) etc. The wTable
function retrieves the financial information from several of Bloomberg's Fundamentals databases. The
retrieved data is displayed in a table, but to make the model visually appealing we also display the results
in a bar graph. To change the display just select the financial benchmark you want to display from a drop down
control. Attached to the drop down control is a simple macro which changes the series which is displayed when
the selection is changed.
To see how the application works, download and install a demo of wTable.
After you have downloaded and installed wTable, you can download a copy of the tip_2001_12.xls workbook.
Our spreadsheet models contain macros and DDE Links. For them to work properly we advise you to download the
model to a local drive and open it with Microsoft Excel rather than opening it with your web browser.
If you are a Bloomberg subscriber, select Yes to update the links, when you open the workbook and you will be
able to use the model to retrieve financial ratios on a group of equity instruments. Because wTable allow you
to use cell referencing, to customize the model to your needs, all you will need to do is change the instrument
codes or fields and the DDE data and calculations dependent upon it will change automatically.
If you are not a Bloomberg subscriber, just select No when asked to update links to information in another workbook.
You will be able to review the macrocode and the Excel functions to see how it works.
Contact us if you would like to employ our services to help you enhance
this model or help you design financial models or customized applications using Bloomberg, Bridge, ILX, Factset
or Reuters data.
|
