This page has been archived and commenting is disabled.

The Fed's Consumer Credit Spreadsheet Is #REFing Out: Here's Why

Tyler Durden's picture




 

As part of the Fed's quarterly report on household debt, the Fed is also kind enough to provide an excel breakdown of the source data that goes into its various charts - certainly a boon for everyone who prefers to work with excel as opposed to powerpoint (used in such hard hitting media products as slideshows of kittens and listicles). Today, however, there was a small problem.

It Refs out.

In fact page after page of Fed data pulled from the excel model located at http://www.newyorkfed.org/householdcredit/2014-q1/data/xls/HHD_C_Report_2014Q1.xlsx refs out (we urge readers to test for themselves).

But why?

Well, as it turns out, before clearing it for public distribution those overlords of central planning, forgot to fix the internal links in the model, and send it out as hard-coded cells. Also known as first year financial analyst rookie error #1.

And where is the excel pulling local data from? As it turns out, an Equifax excel file saved locally in several files such as:

  • C:\equifax\samples\QuarterlyReport\time_series\201403\qrpt_byqtr_201403.out
  • C:\equifax\samples\QuarterlyReport\time_series\201403\qrpt_bystate_201403.out
  • C:\equifax\samples\QuarterlyReport\time_series\201403\bankr_state_201403.xls
  • C:\equifax\samples\QuarterlyReport\time_series\201403\forecl_state_201403.xls

And so on.

Luckily, the fact that the Fed's quarterly report is nothing but a relinked Equifax excel spreadsheet is not a secret. The Fed discloses as much:

This report is based on the New York Fed Consumer Credit Panel, which is constructed from a nationally representative random sample drawn from Equifax credit report data. For details on the data set and the measures reported here, see the data dictionary available at the end of this report. Please contact Matt Mazewski with questions.

What, however, should be a problem is that the Fed's "financial analysts" are so clueless about the most basic use of excel, they let a linked spreadsheet out for public consumption in the process reffing out anyone who tries to recreate their observations.

Aside from that, we have full confidence in the Fed's ability to seamlessly navigate, and centrally-plan, a $17 trillion economy through the perils of "harsh weather", the polar and solar vortex, and, of course, unwinding the biggest central bank balance sheet in history, which at last check accounted for 25% of US GDP and which represents 35% of all 10-year equivalent outstanding Treasurys.

Oh well, blame it on the weather.

Remember: above all, one must have faith.

* * *

That said, we wonder how many minutes after this article is published it will take for the Fed to fix its glaring error.

 

- advertisements -

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Tue, 05/13/2014 - 14:08 | 4755759 Gypsyducks
Gypsyducks's picture

I finally figured out my favorite hashtag

Tue, 05/13/2014 - 14:21 | 4755802 forwardho
forwardho's picture

#Bring our girls back pretty please with sugar on top?

Tue, 05/13/2014 - 14:36 | 4755877 SumTing Wong
SumTing Wong's picture

It ooks like they have fixed it, so less than 32 minutes. Who had that in the pool? Who needs to be paid for guessing correctly?

Tue, 05/13/2014 - 16:12 | 4756258 MillionDollarBoner_
MillionDollarBoner_'s picture

How come they are suddenly "our girls"?

No-one stepped up when Chinese girls were being left out to die under the one child policy.

Or when native Australian girls were being fostered out.

So what makes these "girls" so special?

Oh...wait...

Tue, 05/13/2014 - 16:37 | 4756363 knukles
knukles's picture

Because they're black.

Tue, 05/13/2014 - 14:12 | 4755772 trillion_dollar...
trillion_dollar_deficit's picture

=iferror(ZIRP&QE4EVA)

Tue, 05/13/2014 - 14:12 | 4755773 DetectiveStern
DetectiveStern's picture

Sigh. Select cells. Copy. Paste special values.

Tue, 05/13/2014 - 15:57 | 4756177 Excursionist
Excursionist's picture

Commentary here is a tad harsh...  plenty of current and former sell-siders, from analyst to MD, who couldn't stitch together a three-statement model with a revolver to save their lives.  And those are allegedly among the best and brightest finance professionals our universities crank out.

Probably some frat guy from Fordham working on this, trying to pay off his five-year undergrad tuition bill.  Cut the brotha some slack.

Wed, 05/14/2014 - 13:12 | 4759606 DetectiveStern
DetectiveStern's picture

I spend all day making these kind of reports. The pay is shit I know this haha.

Tue, 05/13/2014 - 14:13 | 4755776 Four chan
Four chan's picture

the old Matt Mazewski trick, i should have known.

Tue, 05/13/2014 - 16:14 | 4756267 MillionDollarBoner_
MillionDollarBoner_'s picture

Matt Mazewski...?...Mazewski..?..what ethnicity would that be... 

Tue, 05/13/2014 - 14:15 | 4755783 0b1knob
0b1knob's picture

ZIRP is sending the US economy towards a division by zero apocalypse.

1 = 2

https://en.wikipedia.org/wiki/Division_by_zero

 

Tue, 05/13/2014 - 14:21 | 4755810 Headbanger
Headbanger's picture

I think it's moar like the imaginary numbers kind of apocalypse

Tue, 05/13/2014 - 16:16 | 4756276 MillionDollarBoner_
MillionDollarBoner_'s picture

More like approaching the Event Horizon...

Tue, 05/13/2014 - 14:31 | 4755849 LawsofPhysics
LawsofPhysics's picture

QE is bad and is theft, bad ZIRP (NIRP in real terms) is far more destructive and inequitable...

But this is being done for a reason as one must destroy the old system before a new one can be introduced.

Tue, 05/13/2014 - 14:16 | 4755789 Fuh Querada
Fuh Querada's picture

Excel is non- validatable, crazily prone to input errors, and has imprecise algorithms for statistical formulas.
Which is, of course, why they use it.
Pros use "R" or SAS.

Tue, 05/13/2014 - 14:20 | 4755800 i_call_you_my_base
i_call_you_my_base's picture

Agree. It's OK for data delivery, but not for modeling / analysis.

I know for a fact that a lot of the big banks use it this way as well. When I saw it I was shocked. They have large systems that just manage the excel models.

Tue, 05/13/2014 - 16:58 | 4756420 NotApplicable
NotApplicable's picture

Funny, how I remember back in the day when I thought VisiCalc was the coolest thing ever. Today? Excel time-bombs. Excel trip-wire bombs. And the best, Excel anti-company mines.

I spend sooooooo much time defusing these things it's ridiculous.

Tue, 05/13/2014 - 14:24 | 4755795 Dr. Engali
Dr. Engali's picture

Why doe it not surprise me that the fed can't use a simple program like excel when we can't even build rockets any longer? De-evolution is a bitch.

Tue, 05/13/2014 - 14:33 | 4755865 Major Major Major
Major Major Major's picture

They recently got rid of the Chinese kid that runs the models as part of new sanctions on China over the new rig

Tue, 05/13/2014 - 17:39 | 4756556 PT
PT's picture

Does anyone remember that charity organization from a few years back  that was trying to supply $100 laptops to kiddies in 3rd world nations?  Those kids are "fully trained up" (as much as anyone could be bothered training them while on a shoestring budget) now and working for US govt for 3 cents per day.  You have to forgive them for the occasional error (forgetting to flatten and Save As jpg, missing bits from spreadsheets), especially since they still live in their mud huts in Africa and so can't really comprehend what it is that they are trying to put together.

Tue, 05/13/2014 - 14:36 | 4755875 pods
pods's picture

The dirty little secret Doc is that Excel is the reason we cannot launch anything into space anymore.

pods

Tue, 05/13/2014 - 14:49 | 4755928 Dr. Engali
Dr. Engali's picture

That is the truth right there. It's amazing how a technologies designed to solve complex problems have dumbed down even the brightest in society. Once the grid goes down there is no rebuilding it.

Tue, 05/13/2014 - 14:59 | 4755968 BlindMonkey
BlindMonkey's picture

Long pine boxes when the last MRI burps and buzzes for the final time.

Tue, 05/13/2014 - 15:33 | 4756084 Jack Sheet
Jack Sheet's picture

you mean all those moon landings in Arizona?

Tue, 05/13/2014 - 14:21 | 4755807 jomama
jomama's picture

because some noob effed up the formula in the spreadsheet?

Tue, 05/13/2014 - 17:42 | 4756564 PT
PT's picture

There are qualified, experienced people out there who know how to use Excel, Illustrator, Photoshop etc...
Some of them are unemployed.

Maybe its alot harder to find knowledgeable people who don't ask too many questions. 

Tue, 05/13/2014 - 14:23 | 4755811 Bunga Bunga
Bunga Bunga's picture

Just tried, the bug is still in. Real numbers must be ugly. The central planning statistics bureau is probably in an emergency meeting to arcade some fake numbers.

But don't worry, when they can fix the economy they can fix such a simple spreadsheet too.

Tue, 05/13/2014 - 14:22 | 4755813 Sudden Debt
Sudden Debt's picture

spreadsheets...
why can't those guys just watch porn like all the rest of us normal people....

Tue, 05/13/2014 - 14:28 | 4755825 Dr. Engali
Dr. Engali's picture

Hey watch how you throw that normal term around. 

Tue, 05/13/2014 - 14:30 | 4755840 Fuh Querada
Fuh Querada's picture

Don't worry! "Spread Sheets" would make a great title for a porn flick.

Tue, 05/13/2014 - 14:38 | 4755885 Sudden Debt
Sudden Debt's picture

Lusty Excell Spreads the sheets ;)

Tue, 05/13/2014 - 14:27 | 4755829 Haywood Yablomi
Haywood Yablomi's picture

Tyler, can we get a post about why the Fed continues to have Reverse Repos at $200B per day?  The volumes did not go down after normal month end window dressing.  Something else is going on.

Tue, 05/13/2014 - 14:38 | 4755889 Winston Churchill
Winston Churchill's picture

Demand for UST's from Zaire.

Tue, 05/13/2014 - 14:40 | 4755896 X_mloclaM
X_mloclaM's picture

yuh down to $75b in gc triparty too

Tue, 05/13/2014 - 14:29 | 4755836 firstdivision
firstdivision's picture

Also looking at the link, the loser analysts at the Fed never heard of Match, Vlookup, Hlookup, or Sumproduct.

I always gave our auditors spreadsheets with massive sumproduct formulas that can easily run out of memory, to do the simplest of calculations that a sumif can.  It got to the point that they quit asking me for data and went to another analyst.

Tue, 05/13/2014 - 14:37 | 4755882 kchrisc
kchrisc's picture

"Booty" trapped spreadsheets. Epic!

Tue, 05/13/2014 - 15:03 | 4755981 BlindMonkey
BlindMonkey's picture

If running out of RAM works for the FAA, it can work for auditors. Well done sir.

Tue, 05/13/2014 - 14:33 | 4755863 Spungo
Spungo's picture

Fucking fiat spreadsheets. We need to go back to using spreadsheets backed with actual excel files.

Tue, 05/13/2014 - 14:49 | 4755938 are we there yet
are we there yet's picture

You need to know the federal excell spreadsheet abbreviations. #REF! stands for 'time for federal accountants to go smoke a refer'. Otherwise their calculations are only understood by your moms cat.

Tue, 05/13/2014 - 14:56 | 4755963 Spungo
Spungo's picture

They realized inflation can make calculations infinitely more complex, so they said fuck it and decided there is no inflation.

Tue, 05/13/2014 - 15:12 | 4756014 Ban KKiller
Ban KKiller's picture

Calling Arthur Anderson! 

Anyone who believes ANY "numbers" from Wall Street or Washington is deluded.  See Enron, WorldComm, etc. 

Tue, 05/13/2014 - 15:13 | 4756018 NYPoke
NYPoke's picture

For years, I've said that entire world finanical system is tracked, in the bowels of the Fed, by an under paid finance geek...and a single spread sheet or several spreadsheets.

 

My stance has not changed.  Sucker.xlsx holds all our debt & every last bill ever printed.

 

When Excel hits its limit, QE will end & the system will collapse.  Apparently, that time is near.

Tue, 05/13/2014 - 15:59 | 4756185 sleigher
sleigher's picture

Well if they are on 32bit windows then that should have happened already...  

Maybe they run windows 3.11

Tue, 05/13/2014 - 15:56 | 4756173 Sticky Wicket
Sticky Wicket's picture

It was actually referencing imaginary numbers in Mr. Yellen's head.

Tue, 05/13/2014 - 15:59 | 4756191 MedicalQuack
MedicalQuack's picture

You can't expect accuracy from the credit folks at all, Experian didn't know they bought a thief when they acquired another data selling outfit either..huge exposure and over 3 millions ran by the thief they bought embedded in the acquisition, secret service had to tell me he existed.

http://ducknetweb.blogspot.com/2014/04/one-really-good-reason-to-license...

There's issue too on the lanscape with mobile apps, seems that the University of Illinois has found a flaw that enables the sensors and their footprint data on the phone to now be linked to your phone ID...scary.  So now look at what the data sellers could get their hands on too...no wonder flawed data is rising so quickly and Equifax in right in there too. 

http://ducknetweb.blogspot.com/2014/05/cell-phone-privacy-tracking-even-...

Tue, 05/13/2014 - 16:11 | 4756255 gcjohns1971
gcjohns1971's picture

When the only possible explanations for what you see are Malevolence or Stupidity.

 

Bet on stupidity every time. 

Stupidity breeds more than malevolence.

Tue, 05/13/2014 - 16:27 | 4756330 mt paul
mt paul's picture

faith

is what happens

 

when you freebase hope...

Tue, 05/13/2014 - 18:05 | 4756671 RaceToTheBottom
RaceToTheBottom's picture

Your analysis as to why the spreadsheets were "REF"ing it, they were operating correctly.

They were just referring to nonexistent collateral.  It was a FED spreadsheet....

Do NOT follow this link or you will be banned from the site!