Lo-Fi Python

Pick Stocks Intelligently with ffn

How do you calculate stock valuation metrics like Sharpe ratio. Recently I've been reading about common stock valuation metrics and wondered how I can apply them to my stock portfolio. I started reading about different metrics, sketching formulas and entertained writing a script to calculate these metrics. But Python has no shortage of finance-related libraries. After some furious googling I found ffn, a way better option than rolling my own formulas. It's a "financial function" library, installable with pip.

It will be interesting to observe how these metrics vary in my portfolio and learn more of ffn's API. I like that they use pandas dataframes within their library because I'm already familiar with them. At minimum, it's good to understand what stock formulas purport to measure and what it means if the measure is low or high. It makes sense to compare stocks in similar industries or competitors like NKE and ADDYY. This is a neat tool for stock nerds who want to level up their stock analysis, make smart decisions and ideally pad the portfolio!

The funny thing is... my lowest university grade was a "C" in my only Finance class. It wasn't easy for me to grasp. But with Python it's a little more interesting and easier to apply. Anyone can level up their finance skills thanks to a cornucopia of finance calculation libraries in the Python ecosystem.

Recommended Reading: A Brief Introduction - ffn documentation

Install ffn with pip:

python -m pip install ffn

Here's the code to get stock data with ffn:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

import ffn

# ffn.get returns a pandas dataframe of market data.
data = ffn.get(
    'tsla,spot,nflx,nke,addyy',
    start='2019-01-01',
    end='2020-12-31'
)
print(data.head())
stats = data.calc_stats()
print(stats.display())

side note on the pyfolio library

I first considered using pyfolio to pull stock data. It is not "out of the box" ready per se to deliver the results pictured in their "single stock" example documentation. You'd need to find another way to fetch your market data or somehow patch the Yahoo Finance API within pyfolio. I preferred ffn, mostly because it worked right away after pip installing it and running the above code.

2024 Update

For a capable finance module, I recommend yfinance. It has worked well for me also. ffn and pyfolio both depend on the Yahoo Finance API, which tends to change and break these libraries. Troubleshooting traceback errors may be required.

Try these other Python financial analysis libraries:

Best Python Libraries/Packages for Finance and Financial Data Scientists

python's built-in statistics module - Backtrader

tia: Toolkit for integration and analysis - FinTA (Financial Technical Analysis)

pandas-datareader - mplfinance - PyPortfolioOpt - TA-Lib Python Port

finsou.py (CLI written by me)

awesome-quant

Image credit, ActiveState: https://www.activestate.com/blog/top-10-python-packages-for-finance-and-financial-modeling/

Post an Unsplash Photo to Facebook Pages with Python

My goal was to automate posting positive photos and quotes to my Facebook page, "Positive Thoughts Daily", with the Unsplash and Facebook APIs. Here's how I did it!

This implementation relies on my own collection of photos on Unsplash. I will manually select which photos I like to get added to my collection. Then my app will take the new photos and post one a day for me.

Side note: the free version of the Unsplash API is capped at 50 requests per week, which was enough for me.

curl -i -X GET "https://graph.facebook.com/oauth/access_token?grant_type=fb_exchange_token& client_id={app-id}& client_secret={app-secret}& fb_exchange_token={short-lived-user-access-token}"

curl -i -X GET "https://graph.facebook.com/{your-user-id}/accounts?access_token={user-access-token}

python -m pip install python-unsplash

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

from unsplash.api import Api
from unsplash.auth import Auth
import requests
"""Python-Unsplash library Github:
https://github.com/yakupadakli/python-unsplash
"""
client_id = "add_your_client_id"
client_secret = "add_your_client_secret"
redirect_uri = "add_your_redirect_uri"
code = ""
auth = Auth(client_id, client_secret, redirect_uri, code=code)
api = Api(auth)
# returns a python list containing a class
image = api.photo.random(collections=66610223) # my collection id
image_id = image[0].id
# Use image_id to get random photo's download link from a collection.
url = f"https://api.unsplash.com/photos/{image_id}/download?client_id={client_id}"
r = requests.get(url)
print(r.text)
image = r.json()
download_link = image['url']

1
2
3
4
5
6

"""Use download link and post to page with Facebook API."""
page_id = "add_page_id_from_about_section"
url = f"https://graph.facebook.com/{page_id}/photos?access_token={page_access_token}&url={download_link}"
r = requests.post(url)
post_ids = r.json()
print(post_ids)

Benefits of Go and Threads in Distributed Systems

Preface

These are my YouTube lecture notes from MIT's Distributed Systems course. Thank you MIT and Professor Morris!

MIT 6.824 Distributed Systems

Lecture 2: RPC and Threads - Feb 7, 2020

Prof. Robert Morris (Spring 2020)

Introduction

Go is a popular programming language choice so my ears perked up when this lecture began. These notes were taken as the professor explains why he teaches his class in Go. He also mentioned he'd be able to teach it with Python or Java. He used C++ years ago.

The beginning of this lecture was a great summary of:

• key benefits of Golang
• what threads are and why they're great
• how Go, threads and async tie together

Go is Good for Distributed Systems

Go is concurrency-friendly. With concurrent threads, you can effectively split a task such as making web requests to a server into many threads, completing them simultaneously.

Golang's Convenient Features and Benefits

• convenient Remote Procedure Call library (RPC) C++ lacks anything comparable?
• "Go is type-safe and memory-safe, unlike C++"
• garbage collection
• "the language is simple, unlike C++"
• "good support for threads, locking and synchronization between threads"
• in Go, "goroutines" are threads
• professor's recommended reading to learn Go: Effective Go

Why use threads?

• I/O Concurrency
• Multi-core Parallelism
• Convenience, e.g. "create 10 threads that sleep for a second and then do a little bit of work"

"Threads are the main tool we're using to manage concurrency in programs."

-Prof. Robert Morris

Contrast WithEvent-driven Programming("Asynchronomous")

A single thread, single loop that waits for an event.

Combining Threads and Event Driven Programming

"Create one thread for each procedure call."... "On each of the threads run a stripped down event driven loop. Sort of one event loop per core. That results in parallelism and I/O concurrency."

-Prof. Robert Morris

Postface: Concurrent Python Context

I've rarely if ever used multiple threads in Python. Simply running a single threaded script seems sufficient for most of my tasks. Maybe I could speed up API requests by splitting into threads when making a few hundred thousand requests? Apparently I'm missing out on concurrent threading efficiency gains.

I once experimented with the multiprocessing module's Process class, which worked on Linux but not Windows for me. I ended up taking an simpler, single thread approach instead. I've also heard of using multiprocessing pool objects. There's also the asyncio library concurrent.futures modules to consider. The ProcessPoolExecutor looks promising.

Python also has the queue module. I haven't used it yet but at one point I watched a talk where Raymond Hettinger recommended queue as a good option if you want concurrency in Python.

It seems there are many options available in Python but it's not clear which tools should be deployed and when. And your chosen concurrency strategy may add extra complexity. Handle with care. Or consider learning Go if you want to use threads to scale your distributed system.

Update: Python Concurrency Success

I recently deployed the ThreadPoolExecutor from the concurrent.futures module to efficiently move thousands of files to a new folder. So Python does have fairly accessible alternatives to concurrency. I guess I'll need to try Go sometime to compare!

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

from concurrent.futures import ThreadPoolExecutor
import numpy as np
import shutil
import os

def main():
    """Move files concurrently from the current working directory to a new folder.
    This script is adapted from the Python ThreadPoolExecutor documentation:
    https://docs.python.org/3/library/concurrent.futures.html#concurrent.futures.Executor.shutdown
    """
    csvs = [f for f in os.listdir(os.getcwd()) if '.csv' in f]
    split_num = len(csvs) / 4 + 1
    file_batches = np.array_split(csvs, split_num)
    # write to local folder named "csvs"
    dst_folder = "/csvs"
    with ThreadPoolExecutor(max_workers=4) as e:
        for i, files in enumerate(file_batches):
            csv_A, csv_B, csv_C, csv_D = files
            e.submit(shutil.move, csv_A, dst_folder)
            e.submit(shutil.move, csv_B, dst_folder)
            e.submit(shutil.move, csv_C, dst_folder)
            e.submit(shutil.move, csv_D, dst_folder)

if __name__ == '__main__':
    main()

Additional Reading

New Case Studies About Google's Use of Go

go.dev

Integrating MySQL with Flask, pandas and pythonanywhere

Sometimes a spark comes from seemingly nowhere. That's when you reach for your tools and create. After a series of successful experiments, I decided this stack might be my quickest, best shot to get a functional website up and running in Python. I was pleasantly surprised to make rapid progress over the span of a quarantine weekend. Here are the steps to create a MySQL backed website with Flask.

Hosting With pythonanywhere

pythonanywhere is a web hosting service like GoDaddy. If you host your app with them, MySQL is the default database. Postgres integration is available at higher price tiers.

To get your Flask app's database up and running you need to:

1. Create your database (see the "Databases" tab in pythonanywhere)
2. Use the mysql terminal to create your tables
3. Use the mysql.connector API to connect to your table and execute SQL from your Flask app.

Essential MySQL Terminal Commands

Show MySQL Version

1	SELECT VERSION();

List tables in a database

1	SHOW TABLES;

Show All MySQL Variable Values

1	SHOW VARIABLES;

Creating a Table

1	CREATE TABLE Marijuana (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(350), date VARCHAR(350));

Create a Table with a JSON column

1 2 3 4 5 6

CREATE TABLE Marijuana ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `store` varchar(200) NOT NULL, `details` json DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;

Add New Column and specify column to insert AFTER

1 2	ALTER TABLE Marijuana ADD COLUMN date VARCHAR(100) AFTER other_column;

Alter Datatype of a Column

1	ALTER TABLE Marijuana MODIFY id INT AUTO_INCREMENT PRIMARY KEY;

Describe a Table

1	DESCRIBE Marijuana;

View All Records in a Table

1	SELECT * from Marijuana;

Using LIKE in MySQL

Select 10 Newest Records

1	SELECT * from Marijuana ORDER BY id DESC LIMIT 10;

"Explaining" A Query

1	EXPLAIN SELECT * from Marijuana;

Using "ANALYZE TABLE" tooptimize themis periodically recommended by MySQL:

1	ANALYZE TABLE Marijuana;

python -m pip3.8 install --user pandas

Flask app with mysql.connector API, SQL and pandas

A Flask app making a mysql database connection with pandas:

1. Creating an error log with logging.
2. Connecting to a mysql database hosted through Flask and pythonanywhere
3. Then reading a table to a pandas dataframe

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84

import mysql.connector from flask import Flask import pandas as pd from datetime import date import logging import sys app = Flask(__name__) logging.basicConfig(stream=sys.stderr, level=logging.DEBUG) @app.route("/") def hello_world(): """Call database and return data from df. Then display homepage.""" try: email_df = get_database_table_as_dataframe() print(email_df.shape) html_page = render_homepage() return html_page except: logging.exception("Failed to connect to database.") def render_homepage(): """Note: you should use Flask's render_template to render HTML files. However, for example you can make a quick f-string HTML page that works in this code. """ html_page = f"""<html><head><link rel='stylesheet' href="/static/styles/some_file.css"><link rel="shortcut icon" type="image/x-icon" href="static/favicon.ico"> <Title>Dispensary Alerts</Title></head> <body><h2></h2> <p>Get alerts for your dope.</p><br> <h6><b>Sign Up</b></h6><br> <div class="form"> <form action="/add_signup_to_db" method="post" style="width:420px;text-align:center;display:block;" > <input type="text" name="Signup Form"> <input type="submit" value="Submit"> </form></div><br><br> <p><b>Current Time:</b> {str(date.today())} </p></body></html>""" return html_page def get_database_table_as_dataframe(): """Connect to a table named 'Emails'. Returns pandas dataframe.""" try: connection = mysql.connector.connect( host="username.mysql.pythonanywhere-services.com", db="username$DatabaseName", user="username", password="password", ) email_df = pd.read_sql(sql="""SELECT * FROM Emails""", con=connection) logging.info(email_df.head()) return email_df except: logging.exception("Failed to fetch dataframe from DB.") return "Oops!" @app.route("/add_signup_to_db", methods=["GET", "POST"]) def add_signup_to_db(email, date): """Pass data as SQL parameters with mysql.""" try: connection = mysql.connector.connect( host="username.mysql.pythonanywhere-services.com", db="username$DatabaseName", user="username", password="password", ) cursor = connection.cursor() sql = """INSERT INTO Emails (message, date) VALUES (%s, %s) """ record_tuple = (email, date) cursor.execute(sql, record_tuple) connection.commit() except mysql.connector.Error as error: logging.info("Failed to insert into MySQL table {}".format(error)) except: logging.exception("Error inserting records to DB.") finally: if connection.is_connected(): cursor.close() connection.close() return "MySQL connection is closed"

Iterative Development

Below: making my website look less like a "my first HTML" website, experimenting with my app's message name and adding a sign-up form connected to the database.

Note: if you see this error when making a request in pythonanywhere:

OSError: Tunnel connection failed: 403 Forbidden

It's likely because you are "whitelisted" on the free plan. Upgrading to the $5/month plan will fix it!

Scoping The Full Stack

I'm really enjoying this web development stack. Here are all of the tools and library choices for this website:

• HTML
• CSS
• web framework: Flask library
• email: Flask-Mail library(SMTP)
• API calls to external websites: requests and json libraries
• data handling: MySQL database, mysql.connector API, pandas library
• file system: logging, os and sys libraries
• (may add) payment processing: Braintree Library
• web hosting: pythonanywhere

Finding Your Flask Groove

Flask is a little scary at first, but reasonable once you get a grasp of the basic syntax. Using the logging module to establish access, error and server log feeds was a big step to finding my Python traceback fixing groove. It's a work in progress.

Recapping My Python Web Development and Database Experiences

I previously created a website with web2py, another Python web framework like Flask and Django. I think it was a decent choice for me at that point in my Python journey. Back then, I connected a MongoDB back-end to web2py. I randomly picked Mongo out of the DB hat and it worked well enough.

My Python Web Development and Database Tools

App #1 web2py + MongoDB

App #2 Flask + MySQL

Future App? py4web + pyDAL + PostgreSQL

Future App? tornado + streamlit (or) Flask + Dash (+ SQLite)

Of these two diverse Python stacks, I favor MySQL and Flask. But I learned a lot from watching web2py's tutorial videos and it's less intimidating for beginners. And I barely scratched the surface of web2py's "pure Python" pyDAL (Database Abstraction Layer), which seems pretty dope.

web2py's creator has a new framework in progress called py4web. It has the same DAL and inherits many other web2py qualities. Definitely looking forward to exploring the DAL on my first py4web website. I'll likely use it to connect to PostgreSQL or SQLite. Maybe I'll install pyDAL with pip in the meantime.

Final Thoughts

Both of my websites are hosted with pythonanywhere, which gives you a text editor and bash terminal to run your scripts in a shell environment. I'm so pleased with all of these tools. They fit together smoothly and made creating my website a fun experience.

A Guide To Making HTTP Requests To APIs With JSON & Python

This contains all of my best API-related knowledge picked up since learning how to use them. All APIs have their own style, quirks and unique requirements. This post explains general terminology, tips and examples if you're looking to tackle your first API.

Here's what is covered:

1. API & HTTP Lingo You Should Know
2. Testing and Exporting Python Request Code from Postman (Optional)
3. Formatting Your Request
4. Example GET and POST Requests
5. "Gotchyas" To Avoid
6. Sidebar: requests.Session()
7. Dig deeper into requests by raising your HTTPConnection.debuglevel

Terminology Clarification: I will refer to "items" or "data" throughout this post. This could be substituted for contacts or whatever data you are looking for. For example, you might be fetching a page of contacts from your CRM. Or fetching your tweets from Twitter's API. Or searching the Google location API, you might look up an address and return geo-location coordinates.

1
2

# headers python dict example
headers = {"Authorization": f"basic {token}"}

python -m pip install requests
python -m pip install pandas

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

import requests


# Find the best double-cheeseburger + fries $7 can buy.
payload = {"key": "Add_Google_API_Key_Here", "address": "Redhot Ranch"}
url = "https://maps.googleapis.com/maps/api/geocode/json"
# Optional: set a 5 second timeout for the http request.
r = requests.get(url=url, params=payload, timeout=5)
print(r.text)
print(r.status_code)
data = r.json()

# Extract the latitude, longitude and formatted address of the first matching location.
latitude = data["results"][0]["geometry"]["location"]["lat"]
longitude = data["results"][0]["geometry"]["location"]["lng"]
formatted_address = data["results"][0]["formatted_address"]
print(longitude)
print(latitude)
print(formatted_address)

# Optional: convert response into a dataframe with pandas.
# import pandas as pd
# location_df = pd.json_normalize(data['results'])
# location_df.to_csv('Locations.csv')

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

import pprint
import json
import requests


def dict_to_json_data():
    """Create request body with fictional contact details."""
    payload = {
        "first_name": "P",
        "last_name": "Sherman",
        "address": "42 Wallaby Way",
        "address_2": "",
        "city": "Sydney",
        "state": "NSW",
        "country": "AU",
        "zip": "2000",
    }
    pprint.pprint(payload)
    json_str = json.dumps(payload, ensure_ascii=True)
    # Optional: encode json str to utf-8.
    return json_str.encode("utf-8")


def post_data(json_str):
    """This is a fictional API request that passes a json object to requests.
    It decodes the server response with response.json() and
    Returns dictionary value by calling the data's keys.
    """
    headers = {
        "Authorization": f"Bearer {token}",
        "Content-Type": "application/json",
        "cache-control": "no-cache",
    }
    r = requests.request(
        method="POST",
        url="https://SomeSoftwareAPI.com/people/",
        data=json_str,
        headers=headers,
    )
    data = r.json()
    print(data.keys())
    # Call dict keys to get their values.
    contact_id = data["contact_id"]
    return contact_id


json_str = dict_to_json_data()
contact_id = post_data(json_str)

1
2
3
4
5
6
7
8
9

import requests


# A session adds a "keep-alive" header to your HTTP connection + stores cookies across requests.
s = requests.Session()
for page in range(0, 2):
    url = f"https://exampleapi.com/widgets/{str(page)}"
    r = s.get(url)
    print(r.text)

1
2
3
4
5
6
7
8
9

from http.client import HTTPConnection
import requests


HTTPConnection.debuglevel = 1
payload = {"key":"Add_Google_API_Key_Here", "address":"90 Miles"}
url = "https://maps.googleapis.com/maps/api/geocode/json"
r = requests.get(url=url, params=payload, timeout=5)
print(r.text)

Automating pytest on Windows with a .bat file, Python, Task Scheduler and Box

Automatic pytest Testing and Failure Alert Monitoring

This is my solution to replace manually running pytest each day in command prompt. I want to automate running pytest every day, test if my automated python scripts ran smoothly and get notified if any tests fail.

Installing pytest, a Python testing library:

python -m pip install pytest

A Few Words on pytest

It is a unit test framework in python. pytest expects you to write each test as a self-contained function. One python file can contain many different test functions.

Writing a Test

Let's use test_file_date.py as our test, which uses the glob module and os.getmtime to get the csv with the most recent modification dateon my desktop. Then it tests if that date is today, in my case, for an expected daily file drop.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

from datetime import datetime, date
import glob
import os
import getpass


def test_csv_date_equals_today():
    """The match format is for a Windows path with Unix style pattern matching."""
    match = f"C:Users/{getpass.getuser()}/Desktop/*.csv"
    # Get the most recent csv from a folder.
    csv = sorted(glob.iglob(match), key=os.path.getmtime)[-1]
    csv_timestamp = os.path.getmtime(csv)
    csv_date = datetime.fromtimestamp(csv_timestamp)
    print(csv_date.day)
    print(date.today().day)
    assert csv_date.day == date.today().day

Here's the pytest text output when the test is passing:

============================= test session starts =============================
platform win32 -- Python 3.8.1, pytest-5.3.5, py-1.8.1, pluggy-0.13.1
rootdir: C:\
collected 1 item

..\..\Users\erick\Desktop\test_file_date.py . [ 14%]
                                                                   [100%]

============================== 1 passed in 0.28s ==============================

Creating a Task with Windows Task Scheduler

If you haven't used python with Windows Task Scheduler before, my previous post on creating a task may help you. We'll create two of them for our testing system.

Adding Your Task Scheduler Program: a Windows .bat file

Add your username to the text below and adjust the paths to your system. Then save a Windows .bat file with this text, which points to your pytest.exe file:

cmd /c "C:\Users\your_username\Desktop\sandbox\Scripts\pytest.exe --capture=sys" ^
C:\Users\your_username\Desktop\test_file_date.py > C:\Users\your_username\Desktop\sandbox\Test_Results\Test_Results.txt

This example is referencing an .exe within a hypothetical "sandbox" virtual environment, located on my Desktop. If you have a virtualenv or venv, check the Scripts folder. (Bin on Linux.)

Breaking this out, there are five .bat files parts:

cmd /c "C:\Users\your_username\Desktop\sandbox\Scripts\pytest.exe --capture=sys"

Windows' cmd command takes a program, so we're passing pytest. The --capture=sys argument tells pytest to capture the test results. Note: switching cmd /c to cmd /k forces the terminal to stay open when you are testing your bat file. You can double-click your .bat file to test run it.

^

circumflex represents a line continuation in Windows batch files for better readability

C:\Users\your_username\Desktop\test_file_date.py

Next we're passing our python file as an argument to pytest, testing our file's modified date matches today's date.

>

This is a Windows redirect. It redirects the pytest output from sys to a text file, which is the last argument in our .bat file:

C:\Users\your_username\Desktop\sandbox\Test_Results\Test_Results.txt

Browse to select your .bat file for your Windows Task Scheduler task:

Reading the Tests and Triggering Alerts

Passing tests signal your scripts are running successfully. When things don't work, email alerts of the failure help us respond quickly.

Let's set another task scheduler job to run read_test_results.py, to run a few minutes after the first job each day. See this example of running Python with Task Scheduler if you haven't triggered a python script from Task Scheduler before.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

from datetime import date
import getpass
import logging
import os

"""Automate pytest with Windows Task Scheduler. Use Task Scheduler run a batch file.
The batch file runs pytest and captures our pytest function results to sys.
If a text file contains a failure or error, write the test contents into a folder.
"""
logging.basicConfig(
    filename="Automated_Testing_Alerts.log",
    level=logging.INFO,
    format="%(asctime)s - %(message)s",
    datefmt="%d-%b-%y %H:%M:%S",
)
logging.info("Checking for failed tests...")
directory = f"C:/Users/{getpass.getuser()}/Desktop/test_results/"
test_results = [fname for fname in os.listdir(directory) if ".txt" in fname]
for txt_file in test_results:
    file_path = directory + txt_file
    with open(file_path) as f:
        text = f.read()
    if "FAILURES" in text:
        directory = f"C:/Users/{getpass.getuser()}/Desktop/send_failure_alert/"
        name = f"{directory}{txt_file}_Failed_Results_{date.today()}.txt"
        with open(name, "w+") as f:
            f.write(name)
            f.write(text)
    else:
        print("No failed tests found in file:")
        print(txt_file)

Setting up Email Alert Notifications on a Box Folder

The above script checks the test results and creates a file with any failed tests in a different folder. I edited the folder's settings to email me when a new file is added, thanks to Box notifications. We use Box for secure file sharing at my day current day job.

Alternatively for those without Box, you could use 'ole trusty smtplib to send the failure alerts with python. I chose the easier, ready to go option. Remember, "Simple is better than complex."

Also, the pywin32 module has an interface to Outlook that is very handy. For an example of sending a Windows Outlook email, check the very end of this post I wrote on "Scripting Windows Like a Pro".

We now have email monitoring alerts if our csv file date test fails, thanks to:

• Windows Task Scheduler (two tasks running each day in succession)
• python/pytest
• a Windows .bat file
• Box (or smtplib or pywin32) email alerts

In Summation

1. The first task runs a .bat file to run pytest and create a text file with daily automation test results.
2. The second task runs a python file. Setting in motion:
3. Checking the test result text files, looking for failed tests
4. Creating a text file with any failing tests in a Box folder, if found
5. Then Box emails me if any test fails.

This was the first time I successfully deployed a Windows batch file. It took me many tries and googling to properly format of the .bat file. They are worth understanding and seem to open up many possibilities on Windows. In this case it was a "glue" piece that allowed me to accomplish my goal, automated testing and alerts for my python scripts.

What we learn along the way shapes us. Learning to use shell commands with Python scripts extends their abilities to help you get stuff done. I've benefitted from improving both my Windows and Ubuntu shell knowledge, which can sometimes be a handy substitute or complement to python. Now, time to write more tests. Thanks for reading!

See also:

pytest-csv: write test results to a csv with this plugin

Read more about software testing in my post here.

Copying a pandas Dataframe to Google Sheets with pygsheets

Disclaimer: This endeavor was before I discovered AppScript, which may be an alternative solution to using pygsheets or other python libraries. pygsheets is interesting, but it could be a stretch to justify using it for something that could be done with AppScript. Both are ways to solve a problem by automating Google Sheet operations.

This was done on the Windows 7 OS. First, install libraries with pip. Enter in command prompt or terminal:

python -m pip install pandas
python -m pip install numpy
python -m pip install pygsheets

After installing necessary libraries, follow the steps documented by pygsheets:

1. Create a Google Developer Account at console.developers.google.com
2. Enable Sheets API to account
3. Enable Drive API to account. Same as last step, but search for Drive.
4. Create a Client Secret json file. Select "Credentials" tab, and "Create Credentials". Select Client Secret from options. Export from console and place in same directory as your .py file.
5. Create a Service Account json file by selecting it instead of "Client Secret".
6. Authorize pygsheets with your json files. See below.
7. Copy spreadsheet to Google Sheet with pandas and pygsheets. See below.

After completing the first 5 steps, import pygsheets and authorize your account with the client secret json file:

1
2

import pygsheets
gc = pygsheets.authorize(client_secret='path/to/client_secret[...].json')

You will be prompted by the terminal to go to a hyperlink in a browser, get your authorization code, and enter that authorization code into the terminal.

Now, import both libraries needed and switch to authorize with your service json file. Then, load the csv to a dataframe with pandas. Finally, copy it to an existing Google Sheet with pygsheets:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

import pygsheets
import pandas as pd

"""Select worksheets by id, index, or title."""
gc = pygsheets.authorize(service_file='path/to/service_account_credentials.json')
sh = gc.open('add_google_sheet_name_here')
wks = sh.worksheet_by_title('add_sheet_tab_name_here')

"""Set a pandas dataframe to google sheet, starting at 1st row, 1st column"""
df = pd.read_csv('Example_Data.csv')
wks.set_dataframe(df,(1,1))

[Example] Split and upload a sheet with 40 columns

Google Sheets limits importing to 26 columns and 1,000 rows at a time. So you'll have to load the sheets in chunks if you have more than that. This approach uses numpy's array_split:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

import pygsheets
import pandas as pd
import numpy as np

gc = pygsheets.authorize(client_secret='path/to/client_secret[...].json')
sh = gc.open('add_google_sheet_name_here')
wks = sh.worksheet_by_title('add_sheet_tab_name_here')
df = pd.read_csv('Data_to_GSheets.csv')

# split columns into two dataframes with numpy and pandas
first_half_cols, second_half_cols = np.array_split(df.columns, 2)
first_half = df[first_half_cols]
second_half = df[second_half_cols]

# set both dataframes side-by-side in Google sheet
wks.set_dataframe(first_half,(1,1))
start_column = first_half.shape[1]
wks.set_dataframe(second_half,(1, start_column))

Conclusion

I found the terminal error messages from pygsheets to be very helpful while debugging the above. This module offers many other nifty spreadsheet operations. Solid library. You can now create and edit Google Sheets with Python.

AppsScript should probably be the default tool when working with Google Sheets because it is built in, but Python does have tools available to work with Google Sheets.

Resources

pygsheets Github

pygsheets Documentation

Google Sheets Documentation

pandas Documentation

Lightning Scripts ⚡

You may or may not be familiar with PyCon's Lightning Talks, a session of quick hitter talks given each year on a variety of topics. These "Lightning Scripts" are ten of my favorite Python scripts that have served me well as of late to perform a variety of tasks.

Highlights include the glob, os, calendar, webbrowser and pandas modules. Plus, how to convert a dataframe into a pdf and how to take a snapshot of a website with pdfkit. The webpage pdf was not the greatest snapshot in some cases, but did capture text and many of a live website's visual elements.

If you have Python installed, paste the script into a text editor and run your .py file from the command line or however you like to run your scripts. You may have to change file paths to work on your operating system.

1
2
3
4

import os
filenames = os.listdir(os.getcwd())
for filename in filenames:
    os.rename(filename, filename.upper())

1
2
3
4
5

import os
folder_contents = os.listdir(os.getcwd())
with open("list_of_dir_files.txt","w") as fhand:
    for item in folder_contents:
        fhand.write('f{item}\n')

1
2
3
4
5
6
7

import calendar
from datetime import date
today = date.today()
if calendar.day_name[today.weekday()] == 'Friday':
    print("Today is Friday.")
else:
    print("Today is not Friday.")

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

import glob
import getpass
import os
# getctime == by created date, getmtime == by date modified
# get all files, regardless of extension
folder = f'C:/Users/{getpass.getuser()}/Desktop/'
list_of_files = glob.glob(folder)
newest_file = sorted(glob.iglob('C:/Users/your_username/Desktop/*.csv'), key=os.path.getmtime)[-1]
second_newest_file = sorted(glob.iglob('C:/Users/your_username/Desktop/*.csv'), key=os.path.getmtime)[-2]
print(newest_file, second_newest_file)

1
2

import webbrowser
webbrowser.open('https://www.luckyseat.com/hamilton-chi/', new=0)

python -m pip install pandas

1
2
3
4
5
6
7

import pandas as pd
file_name = 'big_file.csv'
# slice off extension
src_file_minus_ext = file_name.split('.')[:-1]
for i, df in enumerate(pd.read_csv(file_name, chunksize=250000)):
    csv = f'{src_file_minus_ext}{str(i)}.csv'
    df.to_csv(csv, index=False, header=df.columns)

1
2
3

import pandas as pd
df = pd.read_excel("input.xlsx")
df.to_csv("output.csv", index=False)

1
2
3
4

import pandas as pd
file_name = "data.xlsx"
df = pd.read_excel(file_name)
df.to_html("data.html")

python -m pip install pdfkit

https://wkhtmltopdf.org/downloads.html

sudo apt-get install wkhtmltopdf


**For Windows, **\ `download wkhtmltopdf <https://wkhtmltopdf.org/downloads.html>`__\ ** and add this config patch for pdfkit:**

1
2
3
4

import pdfkit
url = 'https://www.hollywoodreporter.com/news/grammys-alicia-keys-best-moments-as-host-1185013'
config = pdfkit.configuration(wkhtmltopdf='C:\\Program Files\\wkhtmltopdf\\bin\\wkhtmltopdf.exe')
pdfkit.from_url(url, 'webpage.pdf', configuration=config)

1
2

import pdfkit
pdfkit.from_file('data.html', 'report.pdf')

1
2
3

import pdfkit
url = 'https://www.hollywoodreporter.com/news/grammys-alicia-keys-best-moments-as-host-1185013'
pdfkit.from_url(url, 'alicia_keys.pdf')

Making A Desktop Color Eyedropper in Python to Grab Color Values

Goal: recreate my resume in the dark Atom text editor theme (background and fonts). Sub-goal: find a color eyedropper to grab the actual color values of the Atom layout.

Approach #1: find an Atom eyedropper package to grab the colors. My first thought was to find the easiest solution, within the packages of my Atom text editor. After searching Atom's packages, the two best potential solutions were "an-color-eyedropper" and "color picker" . The an-color-eyedropper description sounds perfect: "A simple "real" color picker. By "real" I mean it's able to pick colors anywhere on any screen."

Color picker an color eyedropper

Unfortunately it failed to install and displayed the error, "Unable to download 400 Bad Request Repository inaccessible". It seems to rely on the "python" Atom package which is now deprecated. I was unable to find a repo anywhere by googling.

Color picker has easy-to-follow instructions and installed with no problem. It allows you to quickly select any color visually with sliders. Then the RGB or Hexadecimal values of your color are added as text in the editor in proper format. However, we are looking for a color grabber to pull colors from a screen object. This is more of a productivity enhancing and color exploration tool for programmers. On to Python options.

Approach #2: Use the python tkcolorpicker package to grab the colors.

The first thing I found on Google was tkcolorpicker, a package that uses the tkinter library. I couldn't tell exactly what it was, so let's find out. First, install via pip install:

python -m pip install tkcolorpicker

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

import tkinter as tk
import tkinter.ttk as ttk
from tkcolorpicker import askcolor

root = tk.Tk()
style = ttk.Style(root)
style.theme_use("clam")
hex_code, RGB_code = askcolor((255, 255, 0), root)
print(hex_code, RGB_code)
root.mainloop()

askcolor() returns a tuple with both the RGB and hex codes selected by the user. Above, we are unpacking that tuple into the hex_code and RGB_code variables.

Approach #3: Use the Python eyedropper package to grab the colors.

I then found eyedropper for Windows, which has a minimalist repository and offers a simple approach to desktop eyedropper functionality. Install eyedropper via pip:

python -m pip install eyedropper

Hover your mouse over the object you want to grab the color from (in my case, the Atom text editor background). Alternatively, I was able to run eyedropper from the command line by entering:

py -m eyedropper

Mission possible. Then I hit ctrl+v in a text file and there was the hex code for my Atom background. Some of the colors that eyedropper grabbed were nearly identical to those in the Atom text editor dark theme. Others were not quite the same. I made slight eyeball adjustments to the colors for some of the fonts.

Using Python to convert hex to RGB

Microsoft Word uses RGB codes but eyedropper gave us hex. To convert, I found this website practical and quick. Alternatively, you could convert a hex code to RGB with python:

1 2 3

hex_code = input("Enter hex: ").lstrip("#") RGB_code = tuple(int(hex_code[i : i + 2], 16) for i in (0, 2, 4)) print("RGB =", RGB_code)

Bonus: use pd.read_clipboard() docs to get the hex codes.

Once eyedropper sends the color values to your system's clipboard, there are multiple ways to access them. This alternative uses pandas.

Installing pandas and pyperclip with pip:

1 2	python -m pip install pandas python -m pip install pyperclip

On Linux, install xclip or xsel

sudo apt-get install xclip

To get the clipboard contents with pandas:

1 2 3 4

import pandas as pd hex_code_df = pd.read_clipboard() print(hex_code_df.head())

Supplementary Notes and Links

• Here's a Python eyedropper script featuring the pillow and xlib libraries that I was unable to get working.
• I didn't try ColorCop for Windows, but it may be a non-Python alternative.
• Did you know? Python 2 had a ColorPicker module that is not in Python 3.
• How pandas read_clipboard method works
• pandas to_clipboard and read_clipboard source code
• This desktop tool has great U/X and an eyedropper feature. It runs on Electron, a Javascript based framework. Coincidentally, Electron was used to build Atom.

Gooey GUI for Python Scripts

GUI stands for "Graphical User Interface", aka the part of a program designed for human interaction. Adding a GUI to a Python script allows anyone to run it without having to code or use the command line.

There are several GUI libraries in Python. A few I have heard of are Tkinter (comes in the standard library), wxPython, PyQT, easygui, DearPyGui and PySimpleGUI. I explored Tkinter back when I first got into Python. It was more intricate and offered more control over the look of your app, and took longer to pick up. Gooey is more of a pre-packaged GUI library.

The Gooey Github page was most useful to me and helped me to do what I needed. The script posted in this blog helped as well. I needed to enable a human to supply three files and enter a number. Gooey was a good match for this. The library has two branches:

1. some basic widgets piggyback off the argparse library
2. another part of the library uses a function called the GooeyParser. The GooeyParser offers more advanced widgets, like a file chooser. This was exactly what I was looking to use to pull in files for my script.

Installing Gooey

Argparse comes stock with Python. You can install Gooey via the pip installer. Open command prompt or terminal and enter:

python -m pip install Gooey

Below is a basic argparse/Gooey combination script. The argparse version offers a handful of widgets such as checkboxes and dropdown, but I had trouble getting them to work with the GooeyParser (used in 2nd script).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

from argparse import ArgumentParser
from gooey import Gooey

@Gooey(program_name='Report Generator', default_size=(575, 600))
def get_args():
    """Demonstrating python's vars built-in to store arguments in a python dict."""
    parser = ArgumentParser(description='A simple argument parser', epilog='This is where you might put example usage')
    parser.add_argument('Name', action='store', required=True, help='Help text for option X')
    parser.add_argument('Email', help='Help text for option Y', default=False)
    parser.add_argument('Campaign Number', help='Help text for option Z', type=int)
    parser.add_argument('Campaign Segment', choices=['A', 'B','All'], default='a', nargs='?')
    user_inputs = vars(parser.parse_args())
    print(user_inputs)
    name = user_inputs['Name']
    campaign_number = user_inputs['Campaign Number']
    return parser.parse_args()

if __name__ == '__main__':
    get_args()

Side note: Check out Python's vars() built-in function above. It returns the input data as a dictionary called user_inputs. Then we can get the values via the dictionary's keys. Pretty nifty!

The @Gooey() part of the code is an advanced function known as a decorator in Python. Put simply, decorators are functions that modify the function to which they are attached.

Below is my script that uses the more advanced GooeyParser for its "FileChooser" widget. Gooey allows you to group widgets together and set how many widgets per line with the gooey_options={} parameter.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

from gooey import Gooey, GooeyParser

@Gooey(program_name='Email Campaign Reporting Generator', default_size=(575, 600))
def get_args():
    """Adding two argument groups, each accepting two arguments. Using gooey_options to set layout."""
    parser = GooeyParser(description='Export campaign report spreadsheets and upload below.')
    top_group = parser.add_argument_group(gooey_options={'show_border': False,'columns': 1})
    top_group.add_argument('Contact List', help='Upload Send List (.xlsx)', widget='FileChooser')
    top_group.add_argument('Opens List', help='Upload Opens List (.xlsx)', widget='FileChooser')
    top_group.add_argument('Unsubscribe List', help='Upload Unsubscribe List (.xlsx)', widget='FileChooser')
    bottom_group = parser.add_argument_group(gooey_options={'show_border': False,'columns': 1, 'required':False})
    bottom_group.add_argument('Campaign ID', action='store', help="Number found in the Campaign 'Reports' tab")
    bottom_group.add_argument('Campaign Segment', action='store', help='Enter A, B, or All. All lists supplied must match segment.')
    user_inputs = vars(parser.parse_args())
    name = user_inputs['Name']
    return parser.parse_args()

if __name__ == '__main__':
    get_args()

Overall, Gooey knows what it wants to be, an easy to use GUI framework for Python. It does it well. Here's a screenshot of my program's shiny GUI:

Now that I have a GUI on top of my program and it delivers the expected output file, I'm hoping to take it one step further by packaging it up as a Windows .exe file. This would allow it to run as a desktop app on any Windows computer without the need to install Python or library dependencies. I've only begun exploring options to do this but a few libraries I've heard of are pyinstaller, cx_Freeze and Py2Exe. Updates coming if I figure it out. Cheers :D

Update: I did figure out how to compile my Gooey app to a Windows application with Pyinstaller. You can read more on how I did it here.