Quote from: LoyceV on June 11, 2020, 03:30:34 PM

Background

Quote from: LoyceV on May 15, 2020, 05:11:19 PM

There's another very useful data dump on Blockchair that deserves attention: blockchair_bitcoin_addresses_latest.tsv.gz!

On many occasions I found myself searching for a complete snapshot of all funded addresses, for instance to search for a balance when you only have a partial Bitcoin address. This file takes almost a day to download at 10 kB/s.

The data
See addresses.loyce.club. I keep 18 snapshots of Blockchair's daily data.
Sample: blockchair_bitcoin_addresses_and_balance_DATE.tsv.gz (sorted by balance, highest first (in satoshis)):

Code:

address balance
35hK24tcLEWcgNA4JxpvbkNkoAcDGqQPsP 25550215765875
3KZ526NxCVXbKwwP66RgM3pte6zW4gY1tD 10185724750535
37XuVSEpWW4trkfmvWzegTHQt7BdktSKUs 9450577254951
.......
m-3165957a315e3d9d2de76eccb1140cb8 1
127TnYq7APW8WfKewd7EdxA8gMUXEtr623 1
1E6NkSVsBewyz8Z8wJBYVTKgWmdqcUSWkS 1

This file is in TSV format. It's probably too large (30 million rows) to import into a spreadsheet.

I create a new file with all funded addresses, without balances, and without "weird" addresses (the ones with "-" in it).
Sample: Bitcoin_addresses_DATE.txt.gz (sorted in alphabetical order):

Code:

1111111111111111111114oLvT2
111111111111111111112BEH2ro
111111111111111111112xT3273
.......
bc1qzzzz6hthvpjgtl9pgqyepwwzfrky2ntmm5ccpc
bc1qzzzzp3khtxe32q03qfm6epm5ytyytq7lfcakpn
bc1zqyqsywvzqe

This file is in TEXT format. The first addresses are burn addresses, nobody can access those funds. The (incomplete) last address somehow really got funded.

Download speed should be around 1000 times faster than Blockchair. I don't offer uncompressed downloads.

How to use
The most likely use is to check a long list of Bitcoin addresses for a remaining balance.
On Linux, use this to find matching addresses (after extrating the compressed .gz file of course):

Code:

comm -12 Bitcoin_addresses_LATEST.txt <(cat mylist.txt | sort | uniq)

Bitcoin_addresses_LATEST.txt: the extracted latest version downloaded from addresses.loyce.club.
mylist.txt: your own list of addresses, one address per line.

This takes only seconds to check millions of addresses. If your text file has Microsoft formatting, you may need to use this instead:

Code:

comm -12 Bitcoin_addresses_LATEST.txt <(cat mylist.txt | fromdos | sort | uniq)

If you want the balances of many addresses:

Quote from: LoyceV on October 14, 2022, 09:42:07 AM

Code:

grep -f mylist.txt blockchair_bitcoin_addresses_and_balance_LATEST.tsv

This is a bit slower and eats RAM: 500,000 input addresses uses 1.5 GB RAM and took 50 seconds. You could mix it with the earlier command to only search the balance for addresses that aren't empty.

If you want to know the balance of a certain address, but you don't want any block explorer or SPV wallet on the planet to know you're looking for that address, you can use this:

Code:

wget -qO- http://addresses.loyce.club/blockchair_bitcoin_addresses_and_balance_LATEST.tsv.gz | gunzip | grep -m1 1NXYoJ5xU91Jp83XfVMHwwTUyZFK64BoAD; sleep 5m; kill -9 $$

This doesn't save the file on your local drive, stops downloading after the first match, and kills your Bash shell 5 minutes later (so no .bash_history gets saved). Note (January 12, 2021): I now realize my webserver could know when you stop downloading. If you don't want this, remove "-m1" from grep.
If you want to search more than one address, please download the file first.

If you want to create a brainflayer bloom filter: pbies wrote a guide.

Data retention and updates
I'll provide daily updates. I keep the latest 6 daily snapshots, and the latest 12 monthly snapshots. One a day (or once a month) I delete the oldest files.

New: Direct link to LATEST versions
blockchair_bitcoin_addresses_and_balance_LATEST.tsv.gz (currently 862MB)
Bitcoin_addresses_LATEST.txt.gz (currently 750 MB)

New: Keeping track of the total number of funded addresses
See total_number_of_funded_addresses.txt. Starting December 2020, I'll add a daily total address count to this list. Long-term, this might prove useful (or at least produce nice graphs).

Credits
Blockchair Database Dumps has a staggering amount of data, easily accessible (at 10 kB/s) with daily updates. All data presented in this topic comes from Blockchair.

No spam please.
Self-moderated against spam. Discussion and questions are welcome.

Related topics
Bitcoin block data available in CSV format
List of all Bitcoin addresses with a balance
List of all Bitcoin addresses ever used
[~500 GB] Bitcoin block data: inputs, outputs and transactions
[800 GB] Ethereum data

I tried a lot and could not put to work these commands on linux mint

comm -12 Bitcoin_addresses_LATEST.txt <(cat wallet.txt | sort | uniq)
comm -12 Bitcoin_addresses_LATEST.txt <(cat wallet.txt | fromdos | sort | uniq)
grep -f wallet.txt blockchair_bitcoin_addresses_and_balance_LATEST.tsv