Introduction

In this tutorial, we are going to use gspread python library to access Google Spreadsheets. We are going to see how to set up credentials on your google account and then how to use the python library to access the spreadsheet. At the end of this tutorial, we will give you a full script to save the historical portfolio price each day.

Step 1: Create Credential for OAuth2

We first need to create a JSON file for authentication from Google API console, we will use this file from our python script. Downloading this JSON file could be a little tricky.

• Go to the Google APIs Console.
• Create a new project.
• Click on credentials

• Create a service account key

• When you create the service key, you will be asked for a service. If you don't have one, create it.

• Click create and the JSON file will be downloaded

The JSON file structure should be similar to this one:

{
  "type": "service_account",
  "project_id": "-gdocs",
  "private_key_id": "",
  "private_key": "-",
  "client_email": "",
  "client_id": "",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": ""
}

Step 2: Install google spreadsheet library

You can install gspread with pip:

pip install gspread oauth2client

Note that we also install oauth2client since our authentication method is OAuth2.

Step 3: Authentication to google spreadsheet

The next code will be used to authenticate to google spreadsheets:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('4e8775.json', scope)
client = gspread.authorize(creds)

Note that the json filename (4e8775.json) should be changed by yours. Once we successfully authenticate, we can use the client instance to use spreadsheets.

Step 4: Using the spreadsheets

Opening documents

You can open documents by the name, hash or URL. For example If you have a spreadsheet called "Investments" you can open it with this code:

client.open("Investments")

If you want to open it with URL use:

doc = client.open_by_url('https://...')

Open sheet

Once you have the document open you can use the worksheet method:

investment_sheet = doc.worksheet('historical')

Now on investment_sheet you have the historical sheet.

Cell and row operations

We have our investment_sheet now we can operate on the sheet to update, insert, delete. You can access a cell value using the cell method:

investment_sheet.cell(2, 1).value
investment_sheet.update_cell(2, 1, 'NEW CELL VALUE')

The method cell will access the 2nd row at the 1st column. The second line in the code above will update the cell content on the position (2,1).

To insert a row and multiple columns you can use the method insert_row:

row = ["Column 1 value", "Column 2 value", "Column 3 value"]
row_index
historical_sheet.insert_row(row, row_index)

The insert_row method will add the row at the row_index:

Row iteration with python and google spreadsheets

We can use the range method on the sheet instance and pass the range using spreadsheet column B from row 1 up to 150 (B1:B150):

for historical_data in investment_sheet.range('B1:B50'):
    print(historical_data.value)

Now we are ready for a full example on how to use python and google spreadsheet to record a historical portfolio price.

Getting all togheter with python an gspread

Our script will fetch prices from yahoo finance api. Since most of the python libraries are old and the yahoo finance was updated we will use the api directly. The code will use python requests and you can copy the following google spreadsheet for record historical investment portfolio value

The spreadsheet has the following sheets:

• Dashboard: Just a chart to plot historical portfolio value.
• Investment: On column B put the symbol to fetch and on column C put the amount you own.
• Historical: Here the python script will save the portfolio value each day.

The function update_google_docs will use the information on the investment sheet and the current day to calculate the total, using the fetch_price function. Then it will try to save the value on the historical sheet. The script checks if the historical value for the current day was already added

from datetime import datetime, timedelta

import requests
import gspread
from scraper.settings import config
from oauth2client.service_account import ServiceAccountCredentials

# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)

DOC_URL = 'https://docs.google.com/spreadsheets/d/1-Ii1wVkmZd3eKxXxMiM7ch9hSgF2lMMLnQ_cbF0VYc0/edit?usp=sharing'

def fetch_price(symbol):
    try:
        # we use the yahoo finance api to retrieve symbol data
        url = 'https://query1.finance.yahoo.com/v7/finance/spark?symbols={0}&range=1d&interval=1d&indicators=close&includeTimestamps=false&includePrePost=false&corsDomain=finance.yahoo.com&.tsrc=finance'.format(symbol)
        data = requests.get(url).json()
        # check the json on the url to understand more about how to access it.
        # the idea is to take the first value, which correspond to today
        return data['spark']['result'][0]['response'][0]['indicators']['quote'][0]['close'][0]
    except TypeError:
        raise RuntimeError('Could not find symbol {0}'.format(symbol))

def update_google_docs():
    doc = client.open_by_url(DOC_URL)
    investment_sht = doc.worksheet('investment')
    now = datetime.now()
    now = now.strftime('%d/%m/%Y') # date format for spread sheet
    total = 0
    # our portfolio is saved on investment sheet from row 1 to 150
    for symbol in investment_sht.range('B1:B50'):
        if not symbol.value:
            continue
        amount = float(investment_sht.acell('C{0}'.format(symbol.row)).value)
        current_price = fetch_price(symbol.value)
        total += current_price * amount

    # once we have the total we will save the date and the total
    historical_sht = doc.worksheet('historical')
    index = 0
    for index, row in enumerate(historical_sht.range('A1:A{0}'.format(500000))):
        # we check if the historical data was already added.
        if row.value == now:
            print('Value already added')
            return
        if not row.value:
            # we found the row to insert the new value
            break
    row = [now, total]
    historical_sht.insert_row(row, index + 1)

update_google_docs()