Ease Into XML: Get to Know XML with the XML
Did you know you can update your source data without
re-importing a spreadsheet into Crystal Xcelsius?
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.
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
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
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.
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
Again, if you get the error, don't worry - that just
means this step has already been performed.
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.
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.
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.
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.
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).