GLD Trade Spreadsheet vs GLD Bar List (Updated)

a quick question:

"Are there any significant discrepancies between the numbers in the GLD trade settlement spreadsheet and the GLD bar list?"

Here are the two documents we're talking about:
GLD Trade Settlement Spreadsheet (CSV): This document contains information about the funds Net Asset Value, and has records going back to the start of the fund in 2004. (direct link)
"This file is updated between 6.00 a.m. and 6.30 a.m NYT the following day after trading." For the record - although the spreadsheet gets updated every day, the inventory number may not necessarily change. This document is also the source for @BurningFiat's inventory tweets.

GLD Bar List (PDF from Excel): The bar list reflects T+3, which means changes in the spreadsheet above will normally show 3 trading days later. (direct link)
"The gold bar list is updated at the end every working day (EST)."
We download this file twice a day to check for updates. Each file is usually marked with the published date but it's worth noting the document issue can occasionally skip a day. Also, in approximately 50% of our records, the PDF does change to show a new date, but inventory and bar numbers remain static. Screwtape Files does have the worlds largest public archive of these historical files, links for years 2011- 2014 can be found here.
To answer the question we created an automated download which processes the CSV contents to compare with our BullionBars database. We did find discrepancies, but before we go much further however, note the disclaimer on their website: "The ounces of gold listed on the daily barlist may differ from the ounces of gold listed as owned by SPDR® Gold Trust ("Trust") on the website due to timing differences in trading and settlement.  The barlist includes ounces of gold on a settlement date basis, while ounces shown on the website are on a trade date basis." Fair enough. In fact, the internal PDF time-stamp for the Bar List often shows updates at different times of day, the graph below charts the 'Date Created' by hour, produced less often around lunchtime and even less after 5.00 in the afternoon.

And over time, this time-of-day random distribution hasn't really changed ...

The point is that they issue the Bar list when they're done with it, and not at a set time of day. Anyone who has worked with the pain of managing large excel files will understand and forgive. In any case, this shouldn't matter since eventually the numbers should eventually catch up, with the lag not being more than a day or so. So ideally discrepancies won't be significant. The point of this entire article is that recently I've noticed some diverge between the trade spreadsheet and the bar list document. It's a strange one though.

The comparison is 'NetAssetValueOunces' from the trade spreadsheet, with 'Total Allocated Fine Weight' from the bar list (matching the dates T+3, allowing for weekends), but instead of using the figures at the top of the bar list document, my figures (from the database) get the total fine oz from adding up the weights from every bar listed in the document, I get the following chart for 2014:

So the last time the bar list equalled the trade spreadsheet was on 18th July 2014 and the discrepancy at the minute is approximately 384,000 fine oz, or 1.5 % of the inventory. Even allowing for 'timing differences', the recent divergence strikes me as odd and I can't account for it. I have checked my import routines and I can't see anything out of the ordinary ... if it was an error with my routines I would not expect to see the balanced values for 18th July .. this will probably get corrected again on Monday's bar list, but I do wonder what has caused it.

Conclusion to the question: yes there are divergences but they aren't particularly significant. There is a strange anomaly in recent weeks whereby several hundred thousand oz are not being represented in the PDF, although the totals at the top of the page appear to be correct. We don't know why - although it's most likely due to an 'autofilter' being left on when the PDF gets created from the bar list spreadsheet. I would love to know the significance about those ounces selected/missing. We expect the anomaly to correct itself soon and suspect it is most likely due to under-staffing during the holiday period.

Notes: Data for Divergence figures: link (provided as is). Apologies for the exceedingly dull article ... wake me up when Gold breaks out of  trading range that it's been stuck in since March of this year - then we might finally see some significant inventory changes and correspondingly interesting data. Most of the things I am investigating seem to be dead ends, there is no conspiracy in the data in fact it's stunningly ordinary.

Update 29th July 2014 > archived copy of the bar list Bron used below to verify the 960 missing entries can be found in our GLD2014 archive: link. Also note the bar list from the 28th July 2014 remains unchanged unfixed, contains same discrepancy.

Update 30th July 2014 > bar list from 29th July 2014 link does resolve some of the missing entries (was expected at some point, as per article) but the numbers are still out slightly. I am in the process of processing/reconciling the data and will update this article as soon as I identify the block of 12 pallets. Note this will be tricky because the numbers have changed, but my tools should allow me to isolate it.

Update 2nd August 2014 > As expected, the bar list has finally returned to be in sync with the trade spreadsheet, but the divergence still increased slightly the day prior, a total of 403,563 fine oz. Here's what that last part looked like:

So, what about the bullion that was missing? There doesn't seem to be anything particularly suspicious about it. All the entries were located at the end of the spreadsheet, and for the bars added, there is a 75% dark bullion return rate, which is normal (i.e. 25% was new bullion). The distribution for this is also normal > mostly comprised of Johnson Matthey bars. We can't spot anything else unusual about the entries so the best guess is that the rows were simply missing whatever flag was necessary to make them appear in the final output. Whatever did cause the omission of nearly half a billion dollars worth of gold bars, it was addressed in the bar list published on 29th July 2014 link. I will continue to watch and work with the data to see if the anomaly surfaces again. It does provide some information on how they work with the spreadsheet itself, but I must admit the value of this information is virtually anemic > we can't trade off it. Damn.


Bron Suchecki said...

Dear Barologist,

You say "instead of using the figures at the top of the bar list document, my figures (from the database) get the total fine oz from adding up the weights from every bar listed in the document". As the top of bar list figure = trade date figure on T+3 basis, isn't this the same as saying that the discrepancy is within the bar list itself, independent of any T+ error, that is, the summed total of the bar list doesn't equal the actual sum of the bars listed?

Having just done a quick dump of the current HSBC bar list which says it has 64500 bars, there are only 63540, or 960 short.

The question is what is the filter that takes 960 bars out of the list?

I would suspect it has something to do with settlements - such as the 960 bars are being flagged for future settlement (to be withdrawn) or for movement in the vault.

Then again, maybe is it an unallocated failure due to "unforeseen reasons" :P

Warren James said...

Dear Perth Mint Head Honcho,

You are correct > the bar list is currently under-represented by 960 bars for that particular date. This could be news-worthy depending on the frame of reference ... for example Kirby in 2009 raised the alarm that entries were missing from the list except he messed up didn't notice that although the number of pages had reduced, the number of entries per page had increased. So now this time it has actually happened, he should be all over it. Perhaps KWN will take up the cause :)

I agree ... that filter - whatever it is - may contain some clues to be dug out. What's interesting here is that once the anomaly is fixed, my database will allow me to identify the bars which were missing from the list at the time. IF those match bars which are subsequently removed then we have an interesting situation. If GLD removals are based on the ETF tracking then indeed, how could the removals have been telegraphed before-hand? Of course, that might only mean the flag was for 'stock available to move', but in any case I will be watching this particular data anomaly.


Warren James said...

It's worth mentioning that 960 bars = 80 bars x 12 pallets. While I'm still assuming it's just some sort of clerical error, that's a lot more specific in relation to vault inventory.

Louis Nardozi said...

What else happened at those dates and times? It'd be interesting if those just happened to correlate to price smashes in COMEX gold.

Warren James said...

@Louis, I too am searching for correlations between inventory and price but so far my 4-year research effort has turned up no relationship.

That situation is predicted by Bron Suchecki but I am still searching for evidence to prove or disprove the theory.

If I ever mysteriously stop blogging, you can assume that I did find it and that I was taken out by the cartel.