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).

Close window