Table of Contents
I (like many others) started buying Bitcoin in 2017.
I was buying monthly – in 2017 almost every month included a huge price movement culminating in a $19,000 high in December.
The media noise Bitcoin made every month made it really easy to remember to make my monthly purchase.
As 2018 came around, the speculative bubble popped and the price crashed down to $3,000.
From then on it became incredibly difficult for me to remember to buy monthly because no one was talking about Bitcoin.
That led to me making few investments into Bitcoin in 2018 and missing out on a major opportunity for profit.
Around August 2019 (fueled by the hype of reading Ben Mezrich’s book Bitcoin Billionaires) I finally got serious and automated my Bitcoin purchases with a Python script and Coinbase Pro’s API.
I connected this to my Google Sheet (The Definitive Bitcoin Spreadsheet) that tracks the value of my Bitcoin purchases over time.
I’ve found this spreadsheet to be addictive to look at, but keeping the data in a spreadsheet gives me greater control than any of these silly stock ticker / wallet apps and gives me an ad-free experience.
I hope this article will be user friendly enough that you’ll be able set up the same system quickly and easily.
How to Install Python
Below is a guide to installing Python on Windows/Mac/Linux:
Alternatively: If you have a Mac you can install Python3 using Homebrew detailed instructions here
Now that we’ve installed Python it’s time to get a Coinbase account.
Coinbase Pro API
To sign up for Coinbase Pro you’ll need proof of ID, name, email address, phone number and some other info depending on your region.
Full instructions to sign up for Coinbase Pro are here
Once you’ve signed up for Coinbase Pro, you can activate your API.
Under Permissions I’ve Selected View Transfer and Trade because:
If you want your Python script to automatically dump the trade details into your Google Sheet you’ll need to check the View box
If you want your Python script to automate withdraws + deposits you’ll need to check the Transfer box
If you want your Python script to automate purchases you’ll need to check the Trade box
Record your API Passphrase (ray0ddnl6j in the screenshot) in a text file, we’ll need that for our Python Script to access your Coinbase information.
*** It is important not to share your API passphrase / secret / key with anyone you don’t trust and consider setting up 2 Factor Authentication to further protect your Coinbase Pro account.***
***The API credentials in these screenshots are just demonstrations of what yours might look like.***
That’s all we need from Coinbase so let’s move on to creating the Python script!
Before we get started you’ll need to install the gspread and cbpro Python libraries. You can do this from the terminal on Mac with ‘pip install gspread’ and ‘pip install cbpro’
Below is the code you’ll use to automate your bank transfers, purchase Bitcoin, and dump the information into your Google Sheet.
from oauth2client.service_account import ServiceAccountCredentials
from datetime import date
my_key = 'YOUR API KEY'
my_secret = 'YOUR API SECRET'
my_password = 'YOUR API PASSWORD'
bank_id = 'YOUR BANK ID'
#Populate Transaction Data to Add as New Row in GSheet
transaction_date = str(transaction['created_at'][0:10])
transaction_id = transaction['trade_id']
provider = "Coinbase Pro"
quantity = float(transaction['size'])
btc_price = float(transaction['price'])
fee = float(transaction['fee'])
usd_amount = float(transaction['usd_volume']) + float(fee)
return [transaction_date, transaction_id, provider, quantity, btc_price, usd_amount, fee]
#Access your GSheet
#Set up access to the spreadsheet
scope = ["https://spreadsheets.google.com/feeds",
creds = ServiceAccountCredentials.from_json_keyfile_name("Sheets_creds.json", scope)
client = gspread.authorize(creds)
return client.open("The Definitive Bitcoin Sheet").worksheet("BTC Buy Audit File")
def buyAndPopulateBTC(cb_key, cb_secret, cb_password, bank_id):
buy_size = 0
days_in_month = calendar.monthrange(date.today().year,date.today().month)
auth_client = cbpro.AuthenticatedClient(cb_key, cb_secret, cb_password)
#Deposit if Necessary
if date.today().day == 15: deposit = auth_client.deposit(buy_size*days_in_month, 'USD', bank_id)
#Open the correct spreadsheet + worksheet
audit_file = _authenticateSpreadsheet() btc_fills = list(auth_client.get_fills(product_id="BTC-USD"))
#Get last transaction ID added to worksheet and get a range of days to pull in
last_transaction = audit_file.get_all_records()[-1]['Transaction ID']
#Reverse btc_fills to get dates into Sheets in descending order
for transaction in btc_fills[::-1]:
if transaction['trade_id'] > last_transaction:
cbpro_row = _addTransaction(transaction)
buyAndPopulateBTC(my_key, my_secret, my_password, bank_id)
As it is written, the code above will buy 0 dollars of bitcoin, deposit 0*number of days in the month (October has 31) dollars into your Coinbase account on the 15th of every month, and record the transaction to your Google Sheet Named “The Definitive Bitcoin Sheet” in worksheet “BTC Buy Audit File”.
However, it won’t work as written. You’ll need to fill out your relevant information (API keys + amounts you want to buy + bank info).
You’ll also need to authorize gspread with Google and get a json keyfile (mine is called Sheets_creds.json in the code above)
It will look something like this:
*** Don’t share your credentials with anyone ***
Keep that credentials file in the same directory as your Python script and reference it correctly in the _authenticateSpreadsheet() method.
If you’re stuck, here’s the tutorial I used to get the Google Sheets part working:
Here is a link to the public version of the spreadsheet I built to track transaction data. If you come up with a different format you’ll want to update the _addTransaction method in the Python script so the data comes over cleanly.
At this point in the process I was a little out of my depth.
For a while I just woke up everyday and ran the script from my computer.
That got old fast.
I wanted something that would run the script even if I didn’t have access to my computer (if I’m on vacation I still want to be buying Bitcoin)
Link to that article here
You can get your code from your machine to the AWS Ubuntu machine using GitHub or any other version control software.
Alternatively, there are probably other ways to automate this Python script. If you Bing “Automate Python Script” you’ll find plenty of resources.
Resources and Considerations
Be careful running the script – once you set it up correctly if you have a few too many 0’s in the buy size you’ll run out of money.
The buy_size in the script above is set to $0 so you can test. If you’re extra paranoid you can comment that line out or test with an API (key/secret/password) that doesn’t allow trading.
Below are some developer resources that were helpful to me when getting this up and running.
If there are any questions feel free to leave them below, email me at firstname.lastname@example.org, or tweet at me @rhettre
See you on the moon,
Python CBPro Documentation (Helpful for finding out your Bank ID and other misc data if you want to do something different with your script)
Python GSpread Documentation (Helpful if you have a different spreadsheet layout you want to use)
Cron Timing Calculator (Helpful for getting the Ubuntu cron timing right on AWS)