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.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.
http://www.spdrgoldshares.com/usa/historical-data/ (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.
http://www.spdrgoldshares.com/usa/gold-bar-list/ (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.
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
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.