Bond Post VIII: Marking up Venetian Bonds with XML

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.

Bonds Part VII: Modeling Medieval Venice

After a longer-than-anticipated delay, I’m finally ready to tackle Venetian history with a financial model. After more than a month of painful trial and error programming and countless Google searches for bits of code, I’ve finally finished my financial model (still with its share of bugs). The final result is a simple model that uses data from Sydney Homer’s book, A History of Interest Rates, to generate charts. I’m going to use those charts to analyze Venetian history.

To accomplish this, I made sure to add a few options to the model that allow me to contrast prestiti data from the book with historical events (mainly wars) and the financial activity of the Venetian Republic. With the help of a few references back to my last post, where I summarize Venetian and prestiti history, I’m hoping this post will be a good example of historical analysis with the help of a financial model.

Here’s the plan for this post. I’ll begin by introducing my sources, which are six tables in Homer’s book where he lists information about the prestiti. I’ll explain how I input this data into a spreadsheet that is the foundation for my financial model. After introducing the spreadsheet, I’ll begin to analyze its data with charts generated with the model. The first set of charts will focus on prestiti prices so that I can contrast them with Venice’s historical record. The rest of the charts will include both yields and prices, so I can test if the inverse relationship I described in an earlier post applies to Venetian bonds. I’ll end by considering the limitations of my data and of financial models in general for historical analysis.

Bonds Part VI: An Overview of Medieval Venetian Finance

Every historical era has economic narratives, and sometimes it’s necessary to dig below the surface to find the economic side of the story. I always liked the way that Karl Marx expressed this idea in his writing. For instance at the beginning of his A Contribution to the Critique of Political Economy, he writes,

In the social production of their life, men enter into definite relations that are indispensable and independent of their will…The sum total of these relations of production constitutes the economic structure of society, the real foundation, on which rises a legal and political superstructure…

The important thing to take from this fragment is that beneath the social, political, and legal sides of history, with which most people are familiar, lies an economic layer. Most people I’ve encountered don’t give this underlying layer much thought. Think about historical documentaries and popular history writing that give little attention to the economic conditions of their subject matter.

What’s educational about Venetian history is that the economy was an obvious and unavoidable factor in the city’s rise and fall. The Venetians became a great medieval power with the help of the trading empire they built, and describing that empire requires recounting its underlying economic causes. I’ll incorporate the economy into my narrative of Venice by moving from a general story of the city, to give readers a sense of what Venice was, to the economic forces and financial innovations that propelled it to become a powerful medieval state.

I’ll end my narrative with an overview of the history of the prestiti, the bonds that I’ll analyze in my next post with the help of financial models. The Venetian state eventually came to rely on revenue from these bonds to pay for wars and other services. Wealthy Venetians used the bonds for investments on one of the earliest bond markets in European history. Clearly, the prestiti was an important innovation in early European finance.

Before I begin my Venetian narrative, I want to summarize my sources so readers have a sense of the limitations of my source material and my own interpretations. At the end of this post, I’ll criticize the author of one my sources for not being clear enough about how he uses his sources, so it’s fitting that I subject myself to the same scrutiny.

Bonds Part V: Calculator Code Continued

This post continues the discussion on my bond pricing calculators. In my last blog post, I tried my best to offer a clear and readable account of how my first VBA pricing calculator works. I explained how its code instructs Excel to take bond data from users, with which it calculates and prints the cash flows and final price of a bond on a spreadsheet.

In this blog post, I’m going to discuss the changes I made to that first calculator in order to make it calculate and print a series of yields and bond prices. The goal behind printing this range of numbers is to demonstrate the inverse relationship between yields and prices, according to which, when yields increase, prices fall; when yields fall, prices increase. A spreadsheet with a series of yields and prices is a wonderful visualization, without which I don’t believe I could have fully grasped the relationship.

Bonds Part IV: Bond Calculator Code

In this post, the fourth in my series on bonds, I’ll discuss the VBA program I used to build my first bond pricing model. I used the model in a previous post to demonstrate how bond pricing works, and later I’ll modify it to analyze medieval bonds issued by the Republic of Venice.

The program I’ll discuss here has three goals: (1) take basic information from users about a bond, (2) calculate the bond price and its cash flows, and (3) generate a spreadsheet with the results. Likewise, my code has three main parts. There is what I call (1) its main body, which initiates or calls the other two parts and includes bond calculations; there is (2) the formatting function, which prepares the spreadsheet; and finally there is (3) the userform, which generates a form in which users can type their bond information.

Bonds Part III: Pricing

In my last post in this series, I mentioned that bonds are counter-intuitive and complex. This impression came from news reports I kept reading about the European financial crisis, a typical example of which appears in CNN Money in a report from back in June of 2012: “In a sign of continued jitters over the euro debt crisis, the yield on Spanish 10-year bonds jumped to 7.1%, up from about 6.9% late last week.” I was always confused by why investors would be unhappy with higher interest rates. Doesn’t that mean that they’re earning more from their bonds? Don’t higher interest rates make Spanish bonds more attractive to investors?

I couldn’t adequately answer these questions until I came to understand the inverse relationship between yields and prices. According to this relationship, as yields rise, prices fall; as yields fall, prices rise. In this post, I’m going to explain how this relationship works and, thereby, clarify why investors in Spanish bonds are unhappy.

Bonds, Part II: Introducing bonds

Bonds Part II: The Basics

This post will give a basic introduction to bonds. I’ll begin with a “big picture” perspective on the role bonds play in the wider investing world. Then, I’ll narrow in on the typical characteristics of individual bonds themselves. The hope is that by the end of this post I’ll have set the stage for a more technical discussion in the posts to follow. Bond investing, at least to me, is a counterintuitive and complicated topic, so taking my time here to explain the basics is a necessary first step.

Most fundamentally, bonds are debts with which organizations raise funds. Investors lend their money with the expectation that, after a set period of time has passed, they will receive their money back. What makes such investments worthwhile for investors is that, like any loan, they receive interest payments. Typically, this is a semiannual interest payment until the original value is repaid. For the borrowing organization the benefit is that bonds give access to funds for whatever venture it may have. Also, the organization may not be able to borrow from banks for a lower rate, which means bonds can be a cheap way to raise money.

Bonds, Part I: An Introduction

Bonds, Part I: An Introduction to Series Two

This is an image I found on the University of Nottingham’s homepage of a bond traded in England in the 1500s. The language is Latin, and I hope the image gives a sense of how pre-modern bonds looked.

The topic I’ve decided to tackle for this second blog series is bonds. Most basically, bonds are one of the variety of ways that organizations, both private ventures and government enterprises, raise money. I’ve wanted to write about bonds for at least a few months, but I only found a good historical topic and context for a series after finishing my last blog series. I want to write about the Venetian prestiti, with which the government of Venice in Italy raised money and avoided default. If my memory is correct, the prestiti dates back at least to the end of the 1200s, and I’ve found enough source material about it over the next few centuries to feel that I can build a decent historical model.

An Overview

Of course, I have groundwork to cover before I can discuss the prestiti and Venetian history. The first step will be to introduce bonds and offer a simple financial model or two in VBA to illustrate how bonds work. Unfortunately, bonds aren’t a straightforward financial instrument, so I’m going to take my time to explain them. I’ll begin with the big picture of the place of bonds in investing today and bonds’ most basic characteristics. Then, I’ll move on to more complex topics, which will require discussions of mathematical formulas.

Index for Posts on Amortization Tables

This index lists all of the posts I’ve written about amortization tables and the loan between the Maluenda brothers and the Habsburg crown.

Amortization Tables I, Part I: An Introduction

Amortization Tables, Part II: Starter Spreadsheets

Amortization Tables, Part III: Under the Hood

Amortization Tables, Part IV: A 16th Century Loan and Its Historic and Economic Contexts

Amortization Tables, Part V: Analyzing the Loan

Amortization Tables, Part VI: The Maluenda Loan Code

Amortization Tables, Part VII: Final Conclusions

Amortization Tables VII: Final Conclusions

This is the final post in my series on amortization tables and historical research. I’d like to write down here a few concluding thoughts after a short review of my posts up to this point. The review will make sure that my previous posts are fresh in my mind as I think through and write down my conclusions.

The first blog post was a basic introduction, in which I laid out what I wanted to do with amortization tables and where I quickly introduced the Habsburg monarchy and the loan. In the second post, I went into detail about the purpose and format of amortization tables, and I went through a few examples to illustrate what those tables can do. In the third post, I went under the hood to discuss the VBA program I used to generate my amortization tables for the second post. For my discussion of the program, I was careful to cover basic programming concepts for readers who are new to programming.