Investment-1

Dependencies

library(quantmod)
library(tidyverse)
library(lubridate)
library(googlesheets4)
library(knitr)

investment Data Frame

# Suspend any previous Google authorization
gs4_deauth()
# Import from Google Sheets
raw <- read_sheet('https://docs.google.com/spreadsheets/d/1pMQ1PUSKw4Fd7f21yGokdLx1EuQtn2KAGvaGyrjNU7Q')

## ✔ Reading from "Investments".

## ✔ Range 'Buy'.

investment <- raw %>%
  mutate(Date = as_datetime(Date)) %>%
  select(Date, Symbol, Shares, Price)

Fetch Current Price

price_table <- hashtab()
voo_quote <- getQuote('VOO')
qqq_quote <- getQuote('QQQ')
amzn_quote <- getQuote('AMZN')
goog_quote <- getQuote('GOOG')
sethash(price_table, 'VOO', voo_quote$Last)
sethash(price_table, 'QQQ', qqq_quote$Last)
sethash(price_table, 'AMZN', amzn_quote$Last)
sethash(price_table, 'GOOG', goog_quote$Last)

Calculate

today <- as_datetime(Sys.Date())
holdings <- investment %>% mutate(Original = Price * Shares)
for (row in 1:nrow(holdings)) {
  holdings[row, 'Current'] = price_table[[holdings$Symbol[row]]]
}
holdings <- holdings %>% mutate(Value = Current * Shares)
holdings <- holdings %>% mutate(Profit = Value - Original)
holdings <- holdings %>% mutate(Ratio = Profit / Original, Elapsed = today - Date)
holdings <- holdings %>% mutate(Years = as.numeric(as.duration(Elapsed)) / 31536000)
holdings <- holdings %>% mutate(PPY = ifelse(Years > 1.0, Profit / Years, Profit))
holdings <- holdings %>% mutate(PPYR = PPY / Original)
total_buy <- sum(holdings$Original)
total_profit <- sum(holdings$Profit)
profit_ratio <- total_profit / total_buy
total_ppy <- sum(holdings$PPY)
ppyr <- total_ppy / total_buy
rm(row, today)

Output Table

kable(holdings, caption = 'Holding')

Holding

Date	Symbol	Shares	Price	Original	Current	Value	Profit	Ratio	Elapsed	Years	PPY	PPYR
2023-01-23	VOO	5	364.51	1822.55	371.81	1859.05	36.5	0.0200269	3 days	0.0082192	36.5	0.0200269
2023-01-23	QQQ	4	289.19	1156.76	293.34	1173.36	16.6	0.0143504	3 days	0.0082192	16.6	0.0143504

Visualisation

ggplot(data = holdings, mapping = aes(x = Date, y = Profit, color = Symbol)) + geom_line() + theme_light()

## geom_path: Each group consists of only one observation. Do you need to adjust
## the group aesthetic?

Results

Totally invested is $2979.31. Total profit is $53. Profit ratio is 1.78%. Profit per year is $53. Profit per year ratio is 1.78%. Amount after selling is $3032.31.