Some Usage of Google Colab

As a Python developer I basically never used jupyter, because IPython was good enough. I’ve written related documentation: About Python debugging (IPython).

A few months ago I watched Professor Andrew Ng’s ChatGPT tutorial; its use of colab gave me a lot of inspiration. I suggest readers who haven’t seen it watch it too:

[【Chinese full version, all 9 episodes】Episode 1 Introduction - ChatGPT Prompt Engineering tutorial, Andrew Ng x OpenAI official] https://www.bilibili.com/video/BV1AT41187qt

colab has several advantages:

1. Saved in Google drive — no worries about data loss
2. Beautiful interface, supports Vim key bindings
3. Retains debugging history — you can write code like writing documentation
4. Can use Google’s services as well as local jupyter services

VSCode also supports jupyter notebooks, likewise calling a jupyter server, but you need to save files locally, depend on VSCode, and I feel the pages aren’t as beautiful as Colab’s. So most of the time I use Colab.

Basic Colab Usage

Open https://colab.research.google.com/ and directly create a new notebook:

Some Features

Enabling the ssh Service

I previously wrote a feature providing ssh service; it can also be used in colab — let me plug it again, haha:

upterm modification (1) - supporting VSCode remote connections to any container

!bash <(curl -sL http://corvo.fun/scripts/upterm.sh)

The final effect is as follows:

Connecting to a Local jupyter Service

On the local machine, first install jupyter, then start the server:

jupyter notebook \
  --NotebookApp.allow_origin='https://colab.research.google.com' \
  --port=8888 \
  --NotebookApp.port_retries=0

Google also gives a docker-based approach here:

https://research.google.com/colaboratory/local-runtimes.html

Forwarding Remote Ports to Local

Previously while debugging easytrader — since it only supports Windows — I ran a jupyter server in a Win10 virtual machine, then used ssh to forward the port locally, so I could debug directly with web-based colab.

ssh -L 8888:XXX.XXX.XXX:8888 dev

A Few Case Shares

Database Export and Querying

I discussed this approach with our DBA colleagues and he quite liked it. So I share it here, hoping to help partners with similar needs.

!ls
!pip install ipython-sql
!pip install pymysql
!pip install panda

# load sql module
%load_ext sql

# https://stackoverflow.com/questions/53818698/how-to-remove-connection-string-info-from-ipython-sql-output
%config SqlMagic.displaycon = False


db_url = ''
# mysql+pymysql://user:[email protected]/db_MetaDataMap
with open('db.txt') as f:
  db_url = f.read()
%sql {db_url}

%config SqlMagic.autopandas=True
%sql show tables;

# export query results into a variable
service_list = %sql select index,app_id,name,target from service_list order by app_id;

# pre-process the returned data
import yaml
for name, groups in service_list.groupby('app_id'):
  print(name, groups.to_dict('records'))

The benefit of this approach: you can write Python statements while thinking through the logic, and the notebook records your train of thought. Later, when you need to share the operation process with others, a simple modification does it.

Special character handling

https://stackoverflow.com/a/69482789

I encountered a rather special password aa@bbb&%2021 — the special characters inside need escaping before filling into db.txt:

import urllib.parse
urllib.parse.quote('aa@bbb&%2021')

# 'aa%40bbb%26%252021'
# db.txt mysql+pymysql://root:aa%40bbb%26%[email protected]

Yield Analysis

Previously I tried computing quant strategies, preparing to buy and sell stocks automatically, but needed to calculate yields and such. I know the quant experts all use Excel; I don’t have that skill, so I used Python to simply simulate yield calculation. I feel this code can be reused in future, so I share it simply:

result = 0

## when it rises, sell corresponding quantities of stock per the rise magnitude
steps = [
    # this scheme's yield is roughly 30%, and at a 10% rise the yield requirement is basically met
    [5, 10], # rise 5%, sell 10% of current holdings
    [10, 20],
    [20, 40],
    [50, 100],
]

X = 10000
orig_price = 1.00
total_count = X/orig_price


def print_yield(cur, orig):
    yield_rate = round((cur-orig)/orig * 100, 3)
    print(f"current yield {yield_rate}%")

for (_incr, _sell) in steps:
    cur_price = orig_price * (1+_incr/100)
    result += (cur_price) * (total_count * _sell/100)
    total_count = total_count * (1-_sell/100)
    print(f"retrieved: {result}, current price: {cur_price} remaining count: {total_count}")
    print_yield(result+total_count*cur_price, X)

Summary

Now I find I can’t live without Colab — it has taken over all my work needing simple calculations. It can also take notes for the calculations, and during note-taking you can write code simulating the logic. Compared with complex Excel, programmers probably have more command over Colab.