A couple of days ago I had a post about retail sales. It was titled “Yet Another Article on Retail Sales” because by the time I got around to writing it pretty much everyone had offered their two cents worth. Having said that, I didn’t see any real dollar numbers anywhere else, only nominal dollars. The content for the article can be generated automatically, which comes in handy if you are doing something else at the time that the numbers get released by the US Census Bureau. So in this article I outline some steps to automatically generate the tables and plots when retail sales numbers are released. The tables and plots can be emailed to you, if you are somewhere remote, or emailed to a blog. All you need to do is examine the processed numbers and add your two cents worth with some content.

Step 1 is to download the data. I downloaded seasonally and non-seasonally adjusted retail sales survey data, and CPI numbers. These numbers can be accessed from the US Census Bureau and the Bureau of Labor Statistics. Alternatively you can download them both straight from the St Louis Fed, i.e. from the FRED®. (more…)

mathematica · retail sales

It is relatively common to see economic data plotted with shaded bands highlighting periods of recession. It would be nice to have this feature built into Mathematica, actually it would be nice to have banded plots built into Mathematica. In the meantime here is how you do it.

Firstly have a read of an earlier article about creating banded plots.

Next, head to the National Bureau of Economic Research and get a list of official recession dates. Take those dates and make them into Mathematica date lists.

Next, take some data and pick out the segments that match recessions. This may not be straight forward because economic data is normally reported at the end of the month whereas the recession dating is from the first of the month. You could subtract a day from all the recession dates. Alternatively a work around is shown below where end of month data is converted, for the purposes of matching, to start of the next month:

tmp = data;
tmp1 = Cases[tmp, x_ /; MemberQ[#[[All, {1, 2, 3}]], x[[1, {1, 2, 3}]] /. {a_, b_, c_} -> {a, b + 1, 1}]] & /@ recessions;
tmp1 = DeleteCases[tmp1, {}];

The result is a set of lists, which are subsets of your data, that match recessions periods. You then plot them as described in the article about making banded plots. The advantage of doing it this way is that in addition to making shaded bands you can also control the color of the line, i.e. set a specific color in the banded region. Here is what the end result looks like:

An easier way however is simply to Prolog some rectangle primitives that match recession dates. Firstly, for those not aware, the x axis scale in DateListPlot is in absolute time (seconds). So make a list of absolute time recessions:

absoluteRecessions = AbsoluteTime /@ # & /@ recessions

Once you have that list make a list of “recession rectangles”:

recessionBands1= Rectangle[{First@#, yMin}, {Last@#, yMax}] & /@ absoluteRecessions

or make a function to generate the rectangles:

recessionBands2[yMin_, yMax_] := Rectangle[{First@#, yMin}, {Last@#, yMax}] & /@ absoluteRecessions

Save the list recessionBands1 because you can use it anytime by using a rule replacement to stick in the minimum and maximum y values. Here is the result:

DateListPlot[data,
ImageSize -> 600,
Prolog -> {Yellow, recessionBands /. {yMin -> -4.5, yMax -> 2.5}},
PlotRange -> All
]

If you choose to use a function recessionBands2 to make the bands then just stick in the minimum and maximum y values:

DateListPlot[data[[All, {1, 7}]],
ImageSize -> 600,
Prolog -> {Yellow, recessionBands[-4.5, 2.5]},
PlotRange -> All
]

That’s the end, but below I’ve pasted in the recession date lists and rectangles to save you all some time. (more…)

mathematica

Here is a figure is taken from an ABARE quarterly report.

The bars are the spread, in US dollars per metric ton, between copper prices on the London Metal Exchange (LME) and the Shanghai Futures Exchange (SHFE). The orange line shows China refined copper imports in kilo tonnes. This chart was originally created to show that when arbitrage opportunities exist between the LME and the SHFE, China increases its imports of refined copper. Note that trade on the SHFE is restricted to Chinese firms and commodities held on mainland China.

I wanted to combine that data with some more recent data I had, and that presented a problem — how can I get this data out of the chart. Last year when I read this blog post I thought that what was presented could be modified to created a plot digitizer. Since I didn’t have any plots to digitize I didn’t give it any further thought — until now. After a little bit of trial and error I came up with the function below which has been designed solely for measuring in the y axis only: (more…)

BarChart · copper · LME prices · mathematica

Since the data presented in the article about State’s revenues was annual I decided to download the excel files from the Census Bureau website rather than import them directly but the latter is always an option. The function below to extract the revenue data is straight forward (I think?). Once a broad range of data is extracted I “rule a line” at the “Cash and security holdings” item, data beyond that is not needed.

stateRevenues[year_Integer] :=Module[{year1 = StringDrop[ToString[year], 2], tmp, tmp1, pos},
tmp = Import["/pathToData/" <> year1 <> "states.xls", {"Data", 1}];
tmp = Flatten[{{{year}},
Cases[tmp, {x_String /; StringLength[x] > 0,
y_?IsNumberQ, __} :> {StringTrim[x], makeNumber[y]*10.^-6}]},1];
tmp1 = DeleteCases[tmp, {x_ /; StringMatchQ[x, ___ ~~ "Population" ~~ ___], _}];
pos = First@Flatten@Position[tmp1, "Cash and security holdings"];
tmp1[[1 ;; pos]]
]

This function uses two auxiliary functions that I use frequently with scraped data:

IsNumberQ[x_] := If[NumericQ[x] || StringMatchQ[StringReplace[x, ","->""], NumberString], True, False]

makeNumber[x_] := If[StringQ[x], ToExpression[StringReplace[x, ","->""]], x]

To extract data from all the Excel spreadsheets it is a matter of mapping the years onto the stateRevenues function:

tmp = stateRevenues /@ Range[1996, 2008];

Cases can be used to extract specific information from this data:

revenues =Cases[#, {_Integer} | {"Intergovernmental revenue" | "Taxes" |"Current charges" | "Miscellaneous general revenue" |"Utility revenue" | "Liquor store revenue" |"Liquor stores revenue" | "Insurance trust revenue", _}] & /@ tmp

revenues2 = Rest@Cases[Flatten@#, _?NumericQ] & /@ revenues

From there just make the chart:

p1 = BarChart[revenues2,
AxesStyle -> Directive[FontFamily -> "Helvetica", 13, Plain],
BaseStyle -> Directive[FontFamily -> "Helvetica", 12, Plain],
BarSpacing -> {10, .5},
ChartLayout -> “Stacked”,
ChartStyle -> 61,
ChartLabels -> {Range[1996, 2008], None},
ChartLegends ->
Placed[Style[#, FontFamily -> "Helvetica", 13] & /@ {“Intergovernmental revenue”, “Taxes”,”Current charges”, “Miscellaneous general revenue”, “Utility revenue”, “Liquor store revenue”, “Insurance trust revenue”}, Right],
Epilog -> {Inset[Row[{Style["www.wildebeests.net", ColorData[3, 1],FontFamily -> “Helvetica”, 13, GrayLevel[0.15], Bold]}, Frame -> True, FrameMargins -> 2, Background -> GrayLevel[1]], ImageScaled[{0.12, .15}], {Left, Top}], Inset[Row[{
Style["US State Tax Revenues ($ billion)",FontFamily -> "Helvetica", 13]}], ImageScaled[{0.12, .9}], {Left, Top}]},
Joined -> True,
ImageSize -> 600,
PlotRangePadding -> 0, PerformanceGoal -> “Quality”, Ticks -> {None, Automatic}];

That gives the plot. Next make a label to show the data source:

label1 = Style["\tdata source: US Census Bureau", FontFamily -> "Arial", 10];

I don’t like frames around chart legends and there doesn’t seem an obvious way of removing the chart legend frame (from my reading of the documentation: ref/ChartLegends) so I used a rule replacement to get rid of it … but this really should be done by means of an option:

p2 = Labeled[p1 /. Framed[x__] :> x, label1, {{Bottom, Left}}]

BarChart · Import · mathematica · Processing · Scraping

Just as Moses led his people out of Egypt, many see China leading the world out of an economic slump — although the numbers of those skeptical about China’s continued advance at its current pace seem to be growing.

One of the things I wanted to do was to have China economic data readily available.

I’m long overdue for a follow up to the previous article on Mathematica workflows so I thought I’d discuss the most difficult case I’ve encountered for both retrieval and processing: the National Bureau of Statistics of China.

After some trial and error you can eventually find your way to a page that lists all (?) the data available:.

Enthusiastic users would therefore click on a link and then seek to use Import to get the data directly into Mathematica. Unfortunately when you try and import this data you get $Failed. Save the page and then import it locally and of course you get the same (non)result. After inspecting the HTML code for these pages, which is quite a mess, I can only assume that they are doing something non-standard with their page coding that is causing Import to fail. Since I don’t know the inner workings of Import, and since I don’t know how to recognize, i.e. guess, lines of code that could be causing Import to break this presented a problem.

Eventually after some trail and error this is what I found worked best: (more…)

China

The U.S. Census Bureau released the Advance Monthly Retail Trade and Food Services Survey late last week and on the face of it it seems like good news. By that I mean good news that IS good, whereas “less bad” has often been taken as being good in recent times. Seasonally adjusted retail sales were up 1.3% from the previous month and 1.9% year-on-year. Of course this is a survey of retail sales, with a reported error margin of ±0.5%, not a tally of actual data. So how reliable is it?

At the risk of sounding like I wear a tin-foil hat, should we believe government surveys? I figured that the best way to satisfy myself that this survey gives a credible picture of main street was to compare the survey data with states sales tax receipts. To do this I downloaded all the data via Mathematica and used Mathematica to analyze the data and make the plots shown below.

Quarterly sales tax data reported by the states is collected by the US Census bureau and can be found here. Historical survey data is available here.

The first step in making the comparison was to convert the monthly survey data into quarterly data. The next chart plots total quarterly sales taxes and quarterly retails sales survey data. Note that I’ve included items such as motor fuel sales taxes, and taxes on alcohol and tobacco, in the total sales tax number.

(more…)

retail sales

Given the weakness (freefall?) of the US Dollar I decided to make another series of LME data, this one with Trade Weight Index corrected pricing. The Fed Trade Weight Index (TWI) is published weekly whereas the LME trades daily and is closed on certain holidays. So I wanted to create a list of the TWI for each day of LME trading this decade. There may be easier ways (?) but this is the approach I took:

1. Convert all dates to absolute time.
2. The first LME trading day this decade was on 4th January 2000. Remove TWI data prior to that:

time = AbsoluteTime[{2000, 1, 4, 0, 0, 0.}];
TWIData = DeleteCases[TWIData, x_ /; AbsoluteTime[First@x] < time];

3. Collect the LME trading days for each weekly TWI datum. I figured the easiest way to do this was to use BinLists. Each week has 604800 seconds so this will be the bin width.

bins = BinLists[LMEdates, {start, finish, steps}];

(more…)

LME prices · mathematica · trade weighted index · TWI

Introduction

The WWW is rich with sources of useful data, some of which are available directly, others require registration and subsequent login. I want to discuss how independent investors, without access to Bloomberg, Reuters and other expensive data sources, can streamline their work-flow by automating data access and processing. You’re busy—you want to analyse your data, not spend all your time collecting it and processing it.

I’m going to start by writing about retrieving the data once you have established regular and reliable sources, then discuss processing and presentation. Those stages will all be done by Mathematica, sometimes with a little help from wget during the retrieval stage. The final stage is automation. I run Mac OS X so that stage will describe how to tie everything together in one automated flow. The objective is to set all this happening to a timer and wake up each morning (assuming the data of interest is daily) and have a chart, or several charts, with your data, presented in a design you prefer, ready for you in your email inbox.

Data format

The formats you are likely to want to be acquiring are HTML pages, Excel or CSV files, or zipped files—typically zipped Excel or CSV files. One way to process data available from the web in Excel, or other formats, is to download the data and then import it into Mathematica. You’ll want to be using Mathematica’s Import function. Some additional documentation can be found here.

(more…)

automation · import data · mathematica

I often see plots with vertical bands highlighting segments of the plot. Here’s how to make them in Mathematica.

Starting with a data set of prices we want to highlight segments of the price plot where the correlation with stockpiles is strongly positive. The plot of the price data is made using DateListPlot with some user defined plotting options:

DateListPlot[ prices, options]

Let’s assume that I already have a list of the subsets of this data that we want to highlight. I’ll call these subsets subset1,subset2,… . Next step is to add these subset lists to the list of data:

DateListPlot[ {prices,subset1,subset2,…}, options]

You won’t be able to see the subsets when you plot this unless you specifically set a different styling option for the second and subsequent lists of data that you’re plotting:

DateListPlot[ {prices,subset1,subset2,…}, PlotStyle→{colour1,colour2,colour3,…}]

But this is about creating bands and the easy way to do that is to use the Filling option. If we fill from a subset segment down to the axis plus fill to some point above the plot we can create a continuous highlighting band.

DateListPlot[ {prices,subset1,subset2,…}, Filling→{{2→Bottom}, {2→Top}, {3→Bottom}, {3→Top},…}]

What this syntax is telling us is that we want to fill list number 2 to zero and list number 2 to 5000, the same for the 3rd, 4th and subsequent lists (the first list is our list of prices)

An example of a plot with highlight bands

mathematica

What I’ve set out to do with the Wildebeest Correlation Index is to create a rolling correlation of prices and stockpiles. The correlation data then gets plotted with the line changing colour depending on the correlation: green for negatively correlated data and red for positively correlated data. The input is a list with the form {date, price, stockpile} …

From the initial data we create a temporary list of prices and stockpiles:

temp2 = data[[All,{2, 3}]]

This list is then partitioned into subsets of length chosen by the number, num, of points required for the correlation.The partitioned list is mapped on to the Correlation function:

temp = Correlation[#[ [All,1]], #[[All,2]] ]& /@ Partition[temp2, num, 1]

Next step is to add back the dates and make the plot. To make the line change colour we want to define a ColorFunction which sets the colour to be between green and red depending on the y value of the data:

ColorFunction→Function[{x,y}, Blend[{Green,Red},y]]

The Mathematica shortform notation for this function is written as

ColorFunction→(Blend[{Green,Red},#2]&)

An example of a WCI plot

correlation plot · mathematica · WCI