A story about writing a program that automatically summarizes your own asset transitions

Introduction

I started stocks about half a year ago, but I didn't manage how my assets were changing at all, so I made a program to put them together automatically, so I would like to publish it.

Scraping the amount of assets from the securities company HP

Since I use SBI SECURITIES, the following is how to acquire the amount of assets with SBI SECURITIES. I think that other securities companies can use it if this part is changed (I don't know). Obtain the cash balance from the home screen and the market value (domestic stocks and investment trusts) from the portfolio screen.

** Cash balance on home screen ** Since the amount is written here, I will get it with selenium. It would be nice if there was an API that supported this much, but since there is no API, I will parse and get the muddy HTML. Screenshot at 2020-08-18 21-37-41.png

** Acquisition of market value ** Click here to download the csv file. Obtain the market value by analyzing the csv file (written on the last line of the csv file). *** I wish there was an API that would support me this much. .. .. *** *** Screenshot at 2020-08-18 21-46-58.png

Automatic writing to Google Sheets

I decided to manage the asset transition with Google SpreadSheet.

** Google sheet structure ** Screenshot at 2020-08-18 21-31-24.png

Automatic execution in Cron

If you have stocks, the amount of assets changes every day, but I don't think it's something to record every day, so I only run the program on Sunday at crontab. Executed on time. By the way, it is running on Raspberry Pi.

0 16 * * 0 cd /home/pi/asset-management && python3 run.py
#Minute hour day Monday day command

Where it gets stuck

--Specify the file download destination with selenium --When writing from API with Google Sheet, it is necessary to add SCOPE when creating Credential

What was made

The following is the code.

** Directory structure **

.
├── .credentials.json
├── .sbi.yaml
├── .token.pkl
├── data
│   └── 2020-08-16.csv
└── run.py

** SBI SECURITIES User Information **

yaml:.sbi.yaml


username:SBI SECURITIES'username
password:SBI SECURITIES login password (not transaction password)

** Main program **

run.py


import os
import pickle
from datetime import datetime

import yaml
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

### constants
CHROME_DRIVER_PATH = "/usr/bin/chromedriver"
SBI_URL = "https://www.sbisec.co.jp/ETGate"
SBI_HOME_URL = #URL of SBI SECURITIES'home screen
SBI_ASSET_URL = #URL of the red here
SBI_INFO = ".sbi.yaml"
DOWNLOAD_PATH = os.path.abspath("data")
# google api
CRED_TOKEN = ".token.pkl"
GSHEET_ID = #Google sheet ID
GSHEET_RANGE = "sbi-jp!A1"

# webdriver
driver = None

def setup_webdriver(headless=True):
    global driver
    options = Options()
    options.headless = headless
    options.add_experimental_option("prefs", {"download.default_directory" : DOWNLOAD_PATH})
    driver = webdriver.Chrome(CHROME_DRIVER_PATH, options=options)

    
def quit_webdriver():
    global driver
    driver.quit()
    

def login_to_sbi():
    with open(SBI_INFO, "r") as f:
        info = yaml.safe_load(f)
    driver.get(SBI_URL)
    username_form = driver.find_element_by_id("user_input")\
                          .find_element_by_name("user_id")
    username_form.send_keys(info["username"])
    password_form = driver.find_element_by_id("password_input")\
                          .find_element_by_name("user_password")
    password_form.send_keys(info["password"])
    login_button = driver.find_element_by_name("ACT_login")
    login_button.click()


def get_cash_amount():
    driver.get(SBI_HOME_URL)
    cash = driver.find_element_by_class_name("tp-table-01")\
                 .find_element_by_class_name("tp-td-01")\
                 .find_element_by_tag_name("span")
    return cash.text


def get_market_value(timestamp):
    asset_file = f"{DOWNLOAD_PATH}/{timestamp}.csv"
    driver.get(SBI_ASSET_URL) 
    os.rename(f"{DOWNLOAD_PATH}/New_file.csv", asset_file)
    with open(asset_file, "r", encoding="shift_jis") as f:
        content = f.read()
        ll = content.split("\n")[-2].split(",")
    return ll[0]
    
    

def get_credential():
    creds = None
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly',
              "https://www.googleapis.com/auth/spreadsheets"]
    # The file token.pickle stores the user's access and refresh tokens, and is
    # created automatically when the authorization flow completes for the first
    # time.
    if os.path.exists(CRED_TOKEN):
        with open(CRED_TOKEN, 'rb') as token:
            creds = pickle.load(token)
    # If there are no (valid) credentials available, let the user log in.
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                '.credentials.json', SCOPES)
            creds = flow.run_local_server(port=0)
        # Save the credentials for the next run
        with open(CRED_TOKEN, 'wb') as token:
            pickle.dump(creds, token)
    return creds


def update_gsheet(vals):
    creds = get_credential()
    service = build('sheets', 'v4', credentials=creds)
    body = {"values": [vals]}
    # Call the Sheets API
    resp = service.spreadsheets()\
                  .values()\
                  .append(spreadsheetId=GSHEET_ID,
                          range=GSHEET_RANGE,
                          valueInputOption="USER_ENTERED",
                          insertDataOption="INSERT_ROWS",
                          body=body)\
                  .execute()


def main():
    timestamp = datetime.now().strftime('%Y-%m-%d')
    setup_webdriver()
    login_to_sbi()
    cash = get_cash_amount()
    value = get_market_value(timestamp)
    quit_webdriver()
    update_gsheet([timestamp, cash, value])

    
if __name__ == '__main__':
    main()

Recommended Posts

A story about writing a program that automatically summarizes your own asset transitions
The story of writing a program
Nogizaka46 A program that automatically saves blog images
A story that made it possible to automatically create anison playlists from your music files
Create a program that can generate your favorite images with Selenium
A story about a build error in a shared library that references libusb
I made a program that automatically calculates the zodiac with tkinter
A story about creating a program that will increase the number of Instagram followers from 0 to 700 in a week