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.

Continue reading

Advertisements
Posted in Bonds, Code, Financial Models, Prestiti, Series Two, Spreadsheet, Tables, VBA, XML | Leave a comment

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.

Continue reading

Posted in Bond Pricing, Bonds, Financial Models, Inverse Relationship, Prestiti, Price, Series Two, Spreadsheet, Venice | Leave a comment

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.

Continue reading

Posted in Bond Markets, Bond Pricing, Bonds, Marx, Prestiti, Price, Series Two, Venice | Leave a comment

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.

Continue reading

Posted in Bond Pricing, Bonds, Code, Inverse Relationship, Price, Series Two, Spreadsheet, VBA | Leave a comment

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.

Continue reading

Posted in Bond Pricing, Bonds, Code, Price, Series Two, Spreadsheet, VBA | 2 Comments

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.

Continue reading

Posted in Bond Basics, Bond Markets, Bond Pricing, Bonds, Inverse Relationship, Price, Series Two, VBA, Venice | Leave a comment

Bonds, Part II: Introducing bonds

Bonds Part II: The Basics

Distorted Savings BondThis 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.

Continue reading

Posted in Bond Basics, Bond Markets, Bonds, Series Two | Leave a comment