How to Connect to the BullionBars Database (Updated: October 2011)

This content is dry and boring, and is a technical reference only.  The recent article by Zero Hedge raised some interesting questions, but functionally most people were looking for a historical 'cache' of the GLD bar list so they could verify the existence of bar ZJ6752. I have come to realise that my database is possibly the only public cache of these lists in the world, which is weird when you think about it. My reasoning and goals for doing this are simple - I wish to use hard data to analyse the ETF claims, and attract quality analysis and discussion. The database will help.


As far as the contents go, we have finished the automated download and extraction routines, and written adapters for SLV, GLD, BullionVault and Perth Mint Allocated Pool Silver. We are yet to complete the adapters for GoldMoney and ETF Securities (but we have historical data).


At time of writing, we have the data from quite a lot of documents (we store and process a copy of each PDF when the document has been changed). Originally we started with SLV then got interested in all the other funds as well. Note: the custodian code is my internal 3-letter code.




Historical Data: The original PDF documents used as source are also available to the public. The names of the PDF documents are registered in the 'Documents' table, and have been normalised with a special code indicating the date (self-describing). You can view the files we have made available to the public (the value specified for <Url> is the download link for each individual file). This will probably what people are interested in most, because it gives access to historical bar lists.

*** EDIT MADE September 2012, please use the following page location for PDF links:

http://screwtapefiles.blogspot.com.au/2011/09/how-to-connect-to-bullionbars-database.html
Database Itself: The database contains all the PDF content, basically every bullion bar record is a row in the database, originally from the PDF files. To connect to the database you'll need some software - here are the details of how to connect to it. Please note this is Windows orientated - apologies, I don't use a Mac. First, download a copy of Microsoft SQL Server Management Studio. It's free, but it's chunky and does require some installation prerequisites.

  Database Server: erwzbgqjg0.database.windows.net
  Database Name: BullionBars
  User Name: slvreadonly
  Password: *12345screwtapefiles

Your main connection screen should look like this:
In the second screen under Connection Properties, make sure the database is 'BullionBars'.

UPDATE (Sept 2012): Currently, to minimize hosting costs (as the size of the database grew very rapidly - many many gigabytes) the database is not in the cloud (so you can't connect to it currently) however the server is still active and I have plans to place analysis information in the database in its place.

Disclaimer:
If you wish to use this resource, be sure to read the disclaimer at the bottom of the website here which basically states we are not under any specific obligation to do anything for anyone.

Speed: Multiple logins are allowed, and the database itself is hosted in an east-coast data centre in america so you should find speed quite sufficient.

Expertise Required:
 
This is enterprise-level software and accordingly it requires enterprise-level skills to get in and use it. In order for the resource to be of any benefit, you'll need high quality questions to ask the data.

Expectations:
In the spirit of the exercise, if you get any value from the database, please share what you can with my team here at Screwtape Files.


Copyright:
The data is just data collected from the public domain over snapshots of time. Anyone who wanted to, could have downloaded the bar lists publicly and anonymously. 
In itself the database it is not special and holds no special value apart from the fact that the data has been collated and indexed.

Summary: 
The real value comes from the questions we can ask from the data (like 'has this serial number ever appeared in the GLD bar list?') and these questions like these will form the basis of interesting posts as we look at the data. If you have any questions for connecting, you can comment here, any great suggestions you can still lodge them here, or just send me an email - bullionbars@hotmail.com.

4 comments:

Anonymous said...

Warren,

here is a question.

Everyone knows the LBMA statistics, e.g. on how much gold they clear per trading day. What we don't know is how much of this is physical and how much is unallocated.

The question is whether you can estimate the velocity of the gold in the LBMA system, i.e. (the reciprocal of) how long the average bar stays in one place before it is traded and allocated to somebody else.

You cannot do this for the 'average LBMA bar', but only for those bars that visit one of the ETFs that you are watching. Still, can you get the histogram of durations of bar visits, i.e. for each bar that enters one of the ETFs, how long does it stay there before it leaves. Should the same bar appear again in the future, that is a new event. How does that histogram look like?

I was always interested in the analogous question for coins and retail bars. For example, one would need a coin dealer in a small town in the US that has been in operation since the early 1970s. If you knew their market share, their cummulative purchases and sales (and assume there was no gold around before 1973), you could estimate both the gold stock per capita and the average velocity of retail coins and bars.

Your data are even better because you can identify individual LGD bars (which you cannot do with retail products).

Victor

Warren James said...

Hi Victor, yes the histogram you describe can be produced from the data. I'll create a graph for you in my next data analysis.

An aggregate velocity indicator should emerge from the data and should also vary over time (presumably correlated directly with price volatility). I believe this data is the missing key to the so-called GLD-puke indicator and I am currently searching for it.

p.s. Bron has advised me that bar allocation should be random, so any bar velocity indicators should provide a hunting pattern for tracking down suspicious entries - e.g. bars which never seem to move - based on the idea that such a large sample size should exhibit some form of Brownian motion.

Louis Cypher said...

Just throwing my 2 cents in here as someone who has worked in a warehouse. We would always take the easiest route. That is we would always pick the closest to hand for non perishables. So realistically we should see only new stock movement during quiet times. Old stock will be stuck under a few pallets deep and never see the light of day. The guys in the warehouse should know at this point who are the buy and hold types and will bury their pallets against the furthest wall.
Your Brownian motion may only apply to the say 20% of the stock. Just using the 80/20 rule which may or may not be appropriate. This is going to be a tough nut to crack.

Anonymous said...

Warren,

I don't understand why bar allocation would be random.

Assume I am an Authorized Participant in GLD, and I own a basket of 100000 shares. I give these shares to the trustee (who will then cancel them) and ask for the physical. Now the trustee can decide which bars I will get. So I think Louis is right. The trustee may even have a LIFO list (i.e. stack) of all baskets that went into GLD and will give the warehouse staff the pallet number to fetch for me - the one closest to the door. Their list will even tell them which bars are on that pallet, and so the warehouse staff will only have to *verify* the bar numbers with the list from the trustee, but not to collect these data or go hunting for specific bars.

This is different from a vault in which there are privately owned bars that belong to a large number of customers. For that case, Bron is probably right. You cannot predict who is the next customer to withdraw physical, and he might even ask for a specific bar. So that action would be ranodm.

Victor