Category Archives: financial markets

Bloomberg to Mathematica

Several years ago, my team and I created a tool for importing data via the Bloomberg API directly into Mathematica. It works perfectly and we made both the code and the executable interface available via the Wolfram library. Apparently, Wolfram has removed it, perhaps because it competes with identical functionality in the Wolfram Finance Platform. But the Bloomberg to Mathematica link is free, and a Wolfram Finance Platform license costs $10,000, so people have asked me to put this online somewhere.

So here it is.
[2016 update — I’ve removed the old link as the executables and source code there were outdated. Get the current version at GitHub]

You need to have a Bloomberg Professional license, and as the Bloomberg data API works only on Windows, you need to be running Bloomberg and Mathematica on Windows (I’ve tested this in Windows XP through 8, and with versions of Mathematica through 9.0. Let me know if you run into problems with other setups).

The source code is included here; feel free to make modifications if you like. If you do anything interesting please share it back, it would be nice to see Mathematica used more widely in finance, and free sharing of tools like this would have to help.

Update, 2015:
I have added a new interface notebook for those using Mathematica 10. It fixes some bugs, increases flexibility, and most importantly, takes input and produces output using Mathematica’s new date and temporal data types, and using an Association rather than a list. It’s cleaner and more robust than the old method (though the old notebook will continue to work for those afraid of change.) If you upgrade from the old interface to the new one, note that all the functions have changed names, and their default installation path has as well. It’s all documented in the new notebook in the zip file above.

(Sample use of the old code shown below)

cpi2 = bbg2Mdates[
BBGetNF1["JNCPIYOY Index", "Px_Last", "19820101", "", "monthly"]];

tpxNorm2 =
BBGetNF1["TPX Index", "Px_Last", "19820101", "", "monthly"]]],
name -> "TPX"];

Show[DateListPlot[cpi2[[2]], Frame -> {True, True, False, False},
Joined -> True, Filling -> 0, FillingStyle -> {Blue, White},
GridLines -> False, PlotRange -> All, PlotStyle -> Purple],
DateListPlot[tpxNorm2[[2]], Frame -> {True, True, False, False},
Joined -> True, GridLines -> False, PlotStyle -> Pink],
PlotLabel -> "日本 inflation has been low since \!\(\*
StyleBox[\"before\",\nFontSlant->\"Italic\"]\) the market crashed",
Axes -> {True, False}, PlotRange -> All,
Epilog -> {(Text[
Style["stock market", FontFamily -> "Times New Roman",
FontSize -> 10, Pink], {AbsoluteTime["6/1/1986"],
4.4(*vertical position*)}, {-.01, 0}(*
how close to the end *)]), (Text[
Style["CPI", FontFamily -> "Times New Roman", FontSize -> 10,
Purple], {AbsoluteTime["1/1/1983"],
3.6(*vertical position*)}, {-.01, 0}(* how close to the end *)])}

japan inflation

A demonstration of the new code can be found at

[ Update March 7, 2016: New source code and executables are available on Github, at The Win32 binaries and instructions for use can be found at

If you want to load Bloomberg data into a version of Mathematica older than 10.0, contact me and I can send you an older release. ]

Futures conversion factors in Mathematica

Sovereign debt futures in the U.S. and elsewhere are designed to accept multiple different bonds for delivery. The exchange specifies a delivery factor algorithm to normalize the prices of these bonds (or notes) to make them roughly of equal value upon delivery. Since they’re not precisely of equal value, traders can make money trading around the cheapest to deliver bond.

The conversion factor in the U.S. is designed to convert the yield on any bond or note to 6%. Considering the semi-annual or monthly coupon payments on bonds and notes, the formula to do this looks as follows.

Options[conversionfactor] = {type -> "bond"};
(* versus "10yr note", "5yr note", or "2yr note" *)

conversionfactor[coupon_Real, wholeYearsToMaturity_Integer,
stubMonthsToMatury_Integer, OptionsPattern[]] :=
Module[{v, a, b, c, d},
v = If[stubMonthsToMatury < 7, stubMonthsToMatury, If[OptionValue[type] == "bond" || OptionValue[type] == "10yr note", 3, stubMonthsToMatury - 6]](* for 10 year, other options possible for other instruments *); a = 1/1.03^(v/6); b = coupon/2*(6 - v)/6; c = If[stubMonthsToMatury < 7, 1/1.03^(2*wholeYearsToMaturity), 1/ 1.03^(2*wholeYearsToMaturity + 1)]; d = coupon/.06*(1 - c); a*(coupon/2 + c + d) - b]

wholeYearsToMaturity represents the number of whole years from the first day of the delivery month to the maturity (or call) date of the bond or note.

stubMonthsToMatury represents the number of whole months between wholeYearsToMaturity and the maturity (or call) date rounded down to the nearest quarter for Treasury Bonds and 10 Year Note futures, or to the nearest month for 5-year and 2-year note futures.

For a 2 year note with 1 year, 10 months remaining and a coupon of .015,

In[]:= conversionfactor[.015, 1, 10, type -> "2yr note"]
Out[]= 0.922939

For a 5 year note with 4 years, 10 months remaining and a coupon of .0275

In[]:= conversionfactor[.0275, 4, 10, type -> "5yr note"]
Out[]= 0.86533

Memory conformity

There was a nice article in Science this week about Memory Conformity, the phenomenon whereby people will alter their own memories to conform with versions of events claimed by others. In the study, even people who correctly remembered a test event would persuade themselves that they had seen something different if their peers claimed to have seen it.

The study used brain imaging to try to figure out how this happens, and their results seem so vague as to be of little use. However the phenomenon itself is fascinating, and I wonder if it explains anomalies like the Angel of Mons, a fairy tale about angels appearing in the sky over British troops in WWI, subsequently believed by some of the people who were there.

The theory seems to me relevant to the financial markets, as the number of opinions in the market tend to collapse to a much narrower range than the breadth of experience and knowledge present would seem to justify. Most people abandon their own memory of precedents, and their own intuitive or formal models, and buy into the consensus narrative for why housing prices should be high, or internet stocks, or whatever.

Another old computer — the Powerbook 180c

In 1993, Apple released the PowerBook 180c, which sold for over $4100 new. It was, I think, the final PowerBook with the iconic form factor that had been introduced with the PowerBook 100 in 1991.


Somewhere along the way I picked one up for a percentage point or so of the original selling price. The memory had been upgraded to 14Mb, an amount that would have been astounding at the time, and almost unimaginably expensive, considering the Sumitomo Chemical Plant fire of July 1993 that caused RAM prices to spike. Consider this in comparison to the recent Tōhoku earthquake and tsunami, which put much of northern Japan at least briefly underwater and had virtually no impact on the global economy. In 1993, a fire in one Japanese factory caused computer memory prices to remain elevated for two years.

about this mac 1993

I hauled the computer out of the basement recently to convert some old floppy discs, and put it through some paces online. Unlike the Apple Lisa I recently connected, indirectly, to the web, this machine has a true TCP/IP stack, and can run mail clients, web browsers, and the like.

The machine was made before Apple routinely put ethernet ports on their computers, so an adapter is needed. There probably exist AppleTalk-to-Ethernet adapters that can be connected to the machine’s serial port. I am lucky enough to have an Asante Mini EN/SC 10T adapter, which allows older Macs to be connected to ethernet via their SCSI ports. The driver software was well coded, and the Mac regards the resulting connection as built-in ethernet.

asante en/sc 10T

Network access in Mac OS7 was handled via the MacTCP control panel, not the network control panel, which back then related only to the AppleTalk protocol I think. If you have a similar device and are having trouble configuring it, select “Ethernet Built-In” in MacTCP, then click the “More” button. If you plan to talk to the world, find the names and addresses of your DNS servers and enter them by hand. Your ISP can tell you, if you don’t use OpenDNS or another alternative.

Set your router address (called a “gateway address” here).

I do not think MacTCP handled DHCP correctly. Anyway, I set a manual IP address just in case.

macTCP 1993

Numerous older web browsers and FTP clients, etc., are available online. I used NCSA Mosaic and Fetch. Then I logged into IRC using the venerable Ircle client, which unfortunately killed itself after 30 minutes to punish me for not buying a license.
irc on a PowerBook 180c

Mathematica 2.2 was loaded on the machine, and I ran a couple of quick problems through it.
mathematica 2.2, 1993

The syntax for simple problems like this has not changed, and those problems can be solved using the exact same commands in Mathematica 8.01. On my home Mac Pro, the first problem can be solved a bit better than 8x as quickly on the modern machine, and the second problem about 127x as quickly.

Using NCSA Mosaic, here’s how this website would have looked in 1993:

monkeywrench as rendered in NCSA Mosaic

A silly article from Reuters

Reuters ran an article on the 19th titled “Wall Street’s ‘Buy Everything’ Sentiment Continues” which took as its premise that stock prices in the U.S. are rocketing upwards at an unprecedented pace, and that this, combined with low trading volumes and high levels on the VIX index, means that stocks are “due for a correction”. They quote only one person in the article, Paul Mendelsohn, sage of Charlotte, Vermont. He makes the specific claim that he has “never seen a market like this,” though he has been a “market watcher” for 35 years.

There may be reasonable explanations. Perhaps stocks were too low, so even rising rapidly, they may still be cheap. Or perhaps corporate profitability has increased significantly, so that stocks must rise rapidly to remain fairly valued. Mendelsohn does not consider such issues, saying “I’m showing, by every technical and quantitative standard I have, this market is at extreme levels. But no matter where we start out in the morning, buyers come in.”

Sadly, he doesn’t tell us what “technical and quantitative” standards he has, but the article does give us some clues of what might be worrying Mr. Mendelsohn. It claims that “Trading volume has been exceptionally low recently and the CBOE Volatility Index .VIX, Wall Street’s so-called fear gauge, is up on the week despite the gains in stocks. The index is usually inversely correlated to the S&P 500, and a rise in the VIX typically means a drop in the stock market.”

Hmm.. that sounds wrong.

normal volume

That goes back as far as Bloomberg has the data, and recent volumes don’t look unusually low to me at all. It looks pretty normal, and that volume data is from the New York Stock Exchange, which has been losing share to competing organizations like Liquidnet for years, so total recent volume is certainly higher.

How about the supposedly torrid pace of stock price increases? Reuters worries that “Wall Street posted its third consecutive week of gains with the S&P 500 now up 6.8 percent for the year and more than 20 percent in just six months.”

How weird is this? Let’s consider the 35 years of Mr. Mendelsohn’s expertise.

pretty normal
pretty normal too

It feels hard to panic over that; the current situation looks pretty much like any other bull market. Volume is normal, the pace of increase has plenty of precedent. Through most of the previous periods that looked like this, you’d have been very happy to continue to hold the market.

Oh, about the last claim, that an increase in VIX predicts a drop in the stock market? I have heard this from others, and rigorously testing it is complex (what is the proper lag between an increase in VIX and a drop in the market? Do we care about the absolute level of the VIX, or relative changes therein? etc.) However, as a quick check I just ran normalized VIX against normalized SPX (total return), since January 1990, since that’s when Bloomberg’s data for the VIX starts.

"negatively correlated?"

There’s no evident pattern there, but even the most mindless chart junky willing to trust a regression line however inappropriate has to accept that the Reuters has this one wrong — the best fit is a positive correlation.

All the graphs above come from Mathematica, using the Mathematica link to Bloomberg. The graphs with the superimposed pink rectangles were a little challenging to make; here’s the source code if you want to do something similar —

rangesAndScale =
spxDailyLong[[2]][[i + 33]][[
1]]}, (spxDailyLong[[2]][[i + 33]]/spxDailyLong[[2]][[i]])[[
2]] - 1}, {i, 1,
Length[spxDailyLong[[2]]] - 34}], #[[2]] > .0679 &];

ranges = Transpose[rangesAndScale][[1]];

(* we want overlapping rectangles to be unified into a smaller number of wider rectangles, so we need the following *)

unifier[rangeList_] := Module[{i, outp}, outp = {};
For[i = 1, i <= Length[rangeList], i = i + 1, If[i < Length[rangeList], If[AbsoluteTime[rangeList[[i + 1]][[1]]] <= AbsoluteTime[rangeList[[i]][[2]]], outp = Append[ outp, {rangeList[[i]][[1]], rangeList[[i + 1]][[2]]}]; i = i + 1, outp = Append[outp, rangeList[[i]]] ], outp = Append[outp, rangeList[[i]]]] ]; outp ]

unifiedRanges = FixedPoint[unifier[#] &, ranges] ;

legendRects =
Map[{Pink, Opacity[.5],
Rectangle[{AbsoluteTime[#[[1]]], -100}, {AbsoluteTime[#[[2]]],
1600}]} &, unifiedRanges];

DateListPlot[spxDailyLong[[2]], Frame -> {True, True, False, False},
Epilog -> legendRects,
PlotLabel ->
"\"I've never seen a market like this\"\npink=markets just like \
this (6.78% rise in 34 trading days)"]