Ease Into XML: Get to Know XML with the XML
Map
By David Harper, Principal,
Investor Alternatives, LLC
http://www.investoralternatives.net/
Did you know you can update your source data without
re-importing a spreadsheet into Crystal Xcelsius?
Extensible Markup
Language (XML) defines a format for sharing data across platforms. Because
Excel 2003 and Crystal Xcelsius both offer built-in XML capabilities, you
can use the XML format to feed data dynamically into your Crystal
Xcelsius-built model or dashboard. The advantage is, once you have built
an XML-enabled model, you can forgo the re-import process. Sound good?
Read on.
For this article, I've added XML
functionality to an existing eLearning dashboard I had. The dashboard is
based on an article I wrote for another newsletter explaining why
long-term interest rates remain low, despite twelve consecutive short-term
rate increases by the Fed. The concept is difficult to understand in text,
but I thought it was ideal for a dynamic
illustration.
The idea
is that the interest rate is determined by the intersection of two
supply-demand curves-but instead of supply and demand, the curves are
savings (IS) and money supply (LM). The dashboard lets the user experiment
with more or less savings and more or less money supply in order to view
the theoretical impact of these policy changes on interest
rates.
(Note: you can download all the source files for this
example free at:
http://www.investoralternatives.net/ia/tools.htm)
The Line Chart
Aside from two Play Buttons and a few Text Labels, our example dashboard contains two charts - a Line Chart, and a Bubble Chart stacked directly on top of the Line Chart - and an XML Refresh button. The Bubble Chart is a novelty - it simply creates a bubble where the lines intersect. Here is the Object Browser:
In the underlying spreadsheet, the data that drives the Line Chart is simply two rows: one for the IS curve, and one for the LM curve.
These lines (we could call them curves, except they
are straight) are determined by two sets of two data points: the slope and
the intercept. For any given X-axis number (in this case, Output), the
plotted Y-axis point is equal to the intercept plus (+) the slope
multiplied (x) by the X Output number. You might recall from your algebra
(I know it's been awhile), the equation for a line is y = mx + b. That's
all we are doing here.
Now let's use some
XML. Our chart only needs four pieces of data: two slopes and two
intercepts. For the purpose of this article, I added a fifth data element,
contained in the <when></when> tag that simply supplies the
text for the chart's sub-title.
One of
the example source files (available free at: http://www.investoralternatives.net/ia/tools.htm) is
rateCurves.xml. This is an XML file. So if you have never used XML, this
is your chance to take a peek at the language that everybody is talking
about! You can view this file with your browser or even a plain old text
editor (e.g., notepad). Here is what you should see with Internet Explorer
(of Firefox):
This is XML. It is data that is well-structured and wrapped with tags. The tags are like HTML tags (e.g., <bold>, </p>) except that we can call our tags anything we like. You can see why XML is referred to as "self-describing data." Unlike HTML, we can design our own tags (also called a schema). You can see that the essential structure for this XML file is the following:
The <rates></rates> pair set is the root; it simply holds the whole data set. Then we have a <curve type></curve type> pair that holds the <intercept> and the <slope>. The design is intentionally "Excel-friendly." Excel can take different structures, but a very logical structure for Excel is to embed <row> tags inside the <table> and then each <cell> can hold the values within the rows:
You can see that our XML structure will map to an
Excel table. Why are we going to this trouble, again? Before we take the
actual steps, let's look at the big picture. (Note: the following
explanation is courtesy of Chris Bryant's XML Maps in Excel, available in
the Tech Tips section of the Crystal Xcelsius site's Learning
Center).
First, we design. Then, we deploy. In design, we load
the XML into Excel 2003. Then, this Excel file is imported into Crystal
Xcelsius, and this is the basis of our dashboard or data presentation
(exported to Flash). You are already familiar with these last three steps.
However, once we deploy the Flash file (dashboard), the real magic
happens. The deployed dashboard can pull data directly from the XML source
for updates!
Let's make this happen. There are two sets of
operations we need to perform. First, we need to configure Excel to "map"
data elements from the XML file and into a specified range in Excel. This
is the dynamic link between our XML file and a section within
Excel.
Second, we need to alert Crystal Xcelsius to the fact that
our imported spreadsheet contains some dynamic data (actually, Crystal
Xcelsius is pretty smart and figures this out when we import Excel, so we
are just going to verify).
Big Step #1: Link XML to the Excel Range
1. Open the spreadsheet source file (Rates.xls)
You should see the following:
It looks like ordinary Excel data. But we want to create an XML Mapping in Excel; that is, a link between cells (or ranges) in the spreadsheet and the XML source file. In our sample spreadsheet, we have a small range (from A2:C4) that holds the slope and intercept data. We also have a single cell (A7) that holds the sub-title text. The difference here is that we want our cells to be "mapped" to the XML file, so they will update when the XML file changes. Naturally, we need to tell Excel which cells to "map" to which elements of the XML file.
2. Open the Crystal Xcelsius XML Source task pane.
You can do this in two ways.
One, you can go
to the Crystal Xcelsius menu bar and click on Data > XML >
XML Source...
Alternatively, you can select View
> Task Pane from the menu bar. This brings up the task pane,
but probably shows the Getting Started section. You have to click on the
Getting Started header and select XML Source from the drop-down
menu.
Either of these methods will display the XML Source task
pane, which should look like this (if the folder structure is not
displayed, don't worry, we will populate it in the next step):
3. Ensure that you can see "rates_Map" (as above) in the XML Source task pane.
Even if you have the folder hierarchy above, do this step just to see how it works. Click on the button labeled "XML Maps..." (at the bottom of the XML Source task pane) which brings up the following dialog box:
This dialog box allows us to add XML files to our map. Simply click the "Add..." button and browse to the XML file. In our case, you want to add the rateCurves.xml file. Then select Open to select the file and "OK" to close the XML Maps dialog box.
4. Drag the XML Map elements to the spreadsheet.
Now that we have linked the XML file to the
spreadsheet, we need to tell Excel where to put the XML
data.
(Note: This step has already been done for you, so you
will probably get an error that says "The operation cannot be completed
because the cell or list column is already mapped." That's okay! Excel
won't let you double-map, but I just wanted to show you this important
step)
Drag the curve folder (the folder in the XML Source task
pane; this is the parent folder to the XML elements) to cell
A2:
Again, if you get the error, don't worry - that just
means this step has already been performed.
You can always refresh
the data by right-clicking somewhere in the range, and
selecting XML > Refresh XML Data. This refreshes the
mapped cell contents with the elements from the XML file.
5. Check the XML Map Properties dialog.
This step is also not required for this example, but
it's good to know about this dialog if you have problems.
With your
mouse cursor pointing somewhere in the "mapped" spreadsheet range, perform
a right-click and select XML > XML Map
Properties...
This brings up the following XML Map
Properties dialog box:
Make sure the radio button that says "Overwrite existing data with new data" is selected because, when we update the chart, we want to replace the old slope/intercept data values with new numbers.
6. Save the rates.xls file.
We are done with Excel. To recap: we used the XML
Source task pane to tell Excel that we are pulling in data from an
external XML file. Then we "mapped" elements within the XML file to a
range in the spreadsheet.
Now we can go to Crystal Xcelsius, and as
usual, this is the easy part!
Big Step #2: Link Crystal Xcelsius to the XML
1. Open the Crystal Xcelsius file, rates.xlf, provided in the source files associated with this article.
2. Perform a standard Data > Import Model... and import the rates.xls spreadsheet.
Be sure to import the same rates.xls file above, that we have been working on (This is the typical import step you are already familiar with).
3. Select Data > XML Map Options...
This dialog is probably already correctly set. Here we are telling Crystal Xcelsius where to find the XML file. We could input a relative URL address. Instead, we linked to a cell in our worksheet (cell G2) that contains the relative URL address (which is "\rateCurves.xml").
Notice we left the "Refresh interval in seconds:" set to 0. That means that refresh won't happen automatically. We don't need to automatically update our data for this example; however, if you set this to, say, 10 seconds, then every 10 seconds your Crystal Xcelsius-built model or dashboard will retrieve updated data from the source XML file.
- Select the XML Map Refresh button (which is labeled "Get XML" on the dashboard) to open it's Properties panel.
We are going to use this button instead of setting an automatic refresh interval (as shown in step 2 above). When pushed, this button will retrieve data from the XML file. If we had put a number into the "Refresh interval in seconds:" input above, we would not need a manual button. Notice that the "rates_Map" checkbox is checked.
That's all there is to it. You have now configured
both the Excel spreadsheet and Crystal Xcelsius for the XML connection.
Now, let's test the whole thing. Publish (export) the dashboard to a Flash
(.swf) file, and open the Flash file. You should see the chart with the
default values. All of the numbers are small; e.g., the slope of the lines
are, respectively, -.001 and .001.
Now open the rateCurves.xml with
notepad (or any text editor). It should look something like the short file
below:
Change the second slope from .001 to .002 and insert some new subtitle text in between the <when></when> tags. Now, save this .xml file. Go back to the exported dashboard (Flash file), hit the Get XML button and, presto, your new values should be reflected in the chart. Now that is dynamic!
David Harper is the Principal of Investor Alternatives, LLC, ( http://www.investoralternatives.net/ ) a firm that specializes in investment research, software sector coverage, and derivatives valuation. He is the Editor-in-Chief of Investopedia Advisor ( http://advisor.investopedia.com/ ), a newsletter devoted to the early recognition of public companies that are likely to be lead future market. He publishes the Bionic Turtle Study Notes, courseware for learning advanced risk management concepts. He is a Charted Financial Analyst (CFA) and Financial Risk Manager (FRM).