Entire script for my small M1 Finance project.

m1_googlesheets.R

# ==================================================================================
# M1 Finance/Google Sheets Tracker
# --------------------------------
# Author: Michael Johnson
# Last updated: May 25, 2020
# 
# This script works in tandem with a Python script to easily 
# access and update information relating to my M1 Finance portfolio
# in a Google Sheet. 
# 
# I have updated this script to utilize the most recent version of 
# the Google Sheets API (v4). To use it, you need the code of your 
# spreadsheet (found in the URL) and your authorized Google account. 
# For obvious reasons, I've put this in a separate file. It looks something like this:
#   > portfolio_sheet_code <- "this is the code from the URL"
#   > gs4_auth("[email protected]")
# 
# For some of the range_write() functions, you'll need to know exactly 
# the ranges that you'll be writing the information to. The info_filler()
# function accepts range arguments so that you don't have to dig around in
# the function to automatically update portfolio value. 
# 
# QUICK NOTE: If <<filter>> throws an error, add dplyr:: in front of each instance. 
# If you have loaded the base <<stats>> library after <<dplyr>>, it will cause some 
# funky behavior. If you only run this script in your R session it should be fine. 
# ==================================================================================

# libraries utilized 
# ------------------
library(readr)
library(dplyr)
library(lubridate)
library(googlesheets4)

setwd("~/Documents/M1 Portfolio/")
source("auth_info.R") # this sheet contains the portfolio sheet code and email 

# read the info we need to update our positions
# (obtained from APEX Clearing)
# ---------------------------------------------
positions_info <- read_csv("unrealized.csv")
realized_gains_losses <- read_csv("realized.csv")
dividend_info <- read_csv("activity-export.csv")

# clean up dividend info
# ----------------------
dividend_info$`Net Amount` <- gsub("\\$ ", "", dividend_info$`Net Amount`)
dividend_info$`Net Amount` <- as.double(dividend_info$`Net Amount`)

# get the total dividends 
# for our general sheet
# ------------------------
dividend_info_total <- 
    dividend_info %>%
    dplyr::filter(!is.na(Symbol)) %>%
    group_by(Symbol) %>%
    summarize(`Total Dividends` = sum(`Net Amount`, na.rm = T))

# create the bulk of our general sheet, the rest
# is done using Google Finance API in Sheets
# ----------------------------------------------
sheet_info <- 
    positions_info %>%
    group_by(Symbol) %>%
    summarize(`Average Cost` = sum(`Unit Price` * Quantity), 
              `Total Shares` = sum(Quantity), 
              `Average Price` = (sum(`Unit Price` * Quantity) / sum(Quantity))) %>%
    dplyr::filter(!is.na(Symbol)) %>%
    dplyr::filter(`Total Shares` != 0)

# get the realized gains/losses 
# for open positions that also have
# closed positions mixed in
# ---------------------------------
r_g_l <- 
    realized_gains_losses %>%
    group_by(Symbol, `Buy/Sell`) %>%
    summarize(total = sum(`Short Term Gain/Loss` + `Long Term Gain/Loss`, na.rm = T)) %>%
    dplyr::filter(`Buy/Sell` == "SELL") %>%
    dplyr::filter(Symbol %in% sheet_info$Symbol)

needed_loss_gain <- left_join(sheet_info, r_g_l)

needed_loss_gain <- 
    needed_loss_gain %>% select(total) %>%
    rename(`Realized Gain/Loss` = total)

needed_loss_gain <- 
    needed_loss_gain %>%
    mutate(`Realized Gain/Loss` = ifelse(is.na(`Realized Gain/Loss`), 0, `Realized Gain/Loss`))

# put dividend info in sheet
# --------------------------
dividend_info_sheet <- 
    dividend_info_total %>%
    dplyr::filter(Symbol %in% sheet_info$Symbol)

sheet_info <- full_join(dividend_info_sheet, sheet_info, by = "Symbol")

sheet_info <- 
    sheet_info %>% 
    select(Symbol, `Average Cost`, `Total Shares`, `Average Price`, 
                      `Total Dividends`) %>%
    mutate(`Total Dividends` = ifelse(is.na(`Total Dividends`), 0, `Total Dividends`)) %>%
    arrange(Symbol)

# adjust cells to update sheets
# -----------------------------
range_write(ss = portfolio_sheet_code, data = sheet_info, range = "R Portfolio Analysis!B1", 
            reformat = FALSE)
range_write(ss = portfolio_sheet_code, data = needed_loss_gain, 
            range = "R Portfolio Analysis!I1", reformat = FALSE)

# create monthly dividend info
# ----------------------------

# some date simplification...
dividend_info$`Trade Date` <- as_date(dividend_info$`Trade Date`, format = "%m%Y")

# get year/month of trade to simplify
dividend_info <- 
    dividend_info %>%
    mutate(trade_year_month = format(`Trade Date`, "%Y-%m"))

# get total dividends 
# for each month
dividend_info_monthly <- 
    dividend_info %>%
    dplyr::filter(!is.na(Symbol)) %>%
    group_by(trade_year_month) %>%
    summarize(`Total Dividends` = sum(`Net Amount`)) %>%
    arrange(trade_year_month) %>%
    rename("Date" = trade_year_month)

# get month and date in separate columns
# --------------------------------------
dividend_info_monthly <- 
    dividend_info_monthly %>%
    mutate(month = as.integer(gsub("\\d{4}-", "", Date)), 
           year = as.integer(gsub("-\\d{2}", "", Date)))

# define quarters in month format
# -------------------------------
Q1 <- list(01, 02, 03)
Q2 <- list(04, 05, 06)
Q3 <- list(07, 08, 09)
Q4 <- list(10, 11, 12)

dividend_info_monthly <- 
    dividend_info_monthly %>%
    mutate(Quarter = ifelse(month %in% Q1, "Q1", 
                            ifelse(month %in% Q2, "Q2", 
                                   ifelse(month %in% Q3, "Q3", "Q4")))) %>%
    select(Date, `Total Dividends`, Quarter, year)

# create quarterly info
# ---------------------
quarterly_info <- 
    dividend_info_monthly %>%
    group_by(Quarter, year) %>%
    summarize(`Total Dividends` = sum(`Total Dividends`)) %>%
    arrange(year) %>%
    ungroup() %>%
    mutate(Quarter = paste0(Quarter,"-", year)) %>%
    select(Quarter, `Total Dividends`)

# remove intermediary "year" column
dividend_info_monthly <- dividend_info_monthly %>% select(-year)

# add information to our worksheet
# ----------------------------------
range_write(ss = portfolio_sheet_code, data = dividend_info_monthly, 
            range = "Dividends by Month/Quarter!A1", reformat = FALSE)
range_write(ss = portfolio_sheet_code, data = quarterly_info, 
            range = "Dividends by Month/Quarter!E1", reformat = FALSE)

# dividend_info %>%
#     dplyr::filter(!is.na(Symbol)) %>%
#     group_by(trade_year_month) %>%
#     summarize(`Total Dividends` = sum(`Net Amount`)) %>%
#     arrange(trade_year_month) %>%
#     rename("Date" = trade_year_month)

# get current value of portfolio, then append this 
# to the piece of our sheet that contains the 
# value.
# NOTE: this replaces the other info_filler() 
# function. This one is a bit less complex, as I 
# was overcomplicating it before. 
# ------------------------------------------------
info_filler <- function(gs_code = portfolio_sheet_code, sheet_name = "R Portfolio Analysis!", 
                        value_range = "M:N", portfolio_value_range = "H1:H22"){
    
    current_value <- range_read(ss = gs_code, range = paste(sheet_name, value_range, sep = "")) %>%
        mutate(Date = as.Date(Date))
    
    if(ifelse(today() %in% current_value$Date, TRUE, FALSE) == TRUE){
        cat("Information already exists, operation stopped.")
        stop()
    }
    
    cat("Information doesn't exist, updating.")
    
    test <- data.frame(as.Date(today()), 
                       sum(range_read(ss = portfolio_sheet_code, 
                                      range = paste(sheet_name, portfolio_value_range, sep = ""))))
    
    colnames(test) <- c("Date", "Total Portfolio Value")
    
    current_value <- rbind(current_value, test)
    
    range_write(ss = portfolio_sheet_code, range = paste(sheet_name, value_range, sep = ""), 
                data = current_value, reformat = FALSE)
    
    cat("Information updated.")
}

info_filler()

# to help feel pride and accomplishment
# -------------------------------------
cat("Script done!")
beepr::beep(sound = 3)

Amazing, how do I use it?

Amazing, how do I use it?

I have a writeup here about how I use this script.

I'll probably be revising this gist to use the more recent version of the Google Sheets API in R. It does still work at the moment.

Amazing work! What would be an alternative for people that do not have M1 finance but still looking for semi automated date being populated from a script with stock prices. Instead of pulling it from M1 finance maybe to have those added manually self?

…

On Mon, 20 Apr 2020, 15:05 Michael Johnson, ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ Amazing, how do I use it? I have a writeup here ***@***.***/analyzing-your-m1-portfolio-with-r-and-google-sheets-eabfbb27c691> about how I use this script. I'll probably be revising this gist to use the more recent version of the Google Sheets API in R. It does still work at the moment. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://gist.github.com/1abebff2d84f563e7f6a5dd5325f552f#gistcomment-3261669>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/APIKBLH6U2TTAQDZ736PJR3RNRCCNANCNFSM4MMHWXOA> .

Well it depends on what exactly you want to do.

The bulk of this script is cleanup of the information that comes from the brokerage (primarily using dplyr), and the rest is a pretty straightforward use of the googlesheets package to write the cleaned up tables.

If you have manually created sheets tracking your trades and dividends received, or if you can get that information from a brokerage, then I think this script is a good basis for how to do that if you have prior experience using R and the tidyverse. It just depends on what your data looks like and what your end goal is.