This is the point in this blog series where I discuss the code behind my Venetian bond model. Because my current model includes much more financial information than previous models, I decided now’s a good chance to address the issue of the management of data. Indeed, that’s a subject I’ve been hoping to bring up since I first had the idea for this blog.
I decided, perhaps prematurely, to bring in a new programming language (or markup language, if that’s a more appropriate term) to manage the basic bond data of my financial model. As a reminder, my Venetian bond model generates charts from a spreadsheet of basic financial data. I decided to store its basic info in a file written in a language called XML, which stands for Extensible Markup Language.
In this post, then, I’m going to begin with a general introduction to XML. I want readers with no experience with it to understand enough so that they can in a general way make sense of most XML files they may encounter. I’ll use that foundation to introduce my own XML file, which will involve an overview of its basic structure and a tutorial on how to generate large XML files with the help of Excel.
I’ll then discuss the two lines of VBA code in my model that generate the spreadsheet. One of those lines imports XML into the sheet, and the other formats the resulting table so that it’s more readable than the default color scheme Excel gives it.
Unlike previous posts on code, I won’t paste all of my code, both for my XML and VBA files, at the top of this post. The XML file alone has probably over 1,000 lines, so it’s better to paste relevant sections as they come up for my commentary. Still, I’ve included a link below to my VBA file, which needs slight modifications to work properly. I’ll explain those modifications as they come up in this and the blog posts.
The VBA file: Prestiti Bonds
WordPress didn’t allow me to link to the XML file due to security issues, so in the tutorial below I’ll show readers how they can create their own XML file with the help of Notepad and Excel.