CODEWITHSUNDEEP
rdplyrquantmod
Vasco de Gama
Vasco de Gama

Reputation: 49

Quantmod, getSymbols, Extracting Open,Close Price, Volume in R

I am trying to generate an excel file containing for each stock ticker (in column 1) - open, close, high, low, volume. So far i extracted a list of tickers (from another excel file, column "Symbol") with the following function:

library(readxl)
library("dplyr")
 
stocks_data <- read_excel(file.choose())
                       
v1<-pull(stocks_data,Symbol)

Next i would like for each ticker in list V1 create additional columns with Open, Close, Volume etc (for last closing day only, then save it all in one excel file). Is there a quick function to accomplish that? Thank you.

Desired output:

enter image description here

Upvotes: 1

Views: 499

Answers (1)

jpdugo17
jpdugo17

Reputation: 7106

We can download a list of stocks and extract the last row from each xts object.

library(quantmod)
library(tidyverse)

tickers <- c('ATVI', 'TTWO')
stocks <- new.env()

ohlcva <- c("Open", "High", "Low", "Close", "Volume", 'Adjusted')


getSymbols.yahoo(tickers,env = stocks)
#> [1] "ATVI" "TTWO"


tbl <- eapply(stocks, function(.x) xts::last(.x) %>% as_tibble %>%  set_names(ohlcva)) 

tbl <- 
imap(tbl, ~mutate(.x, Symbol = .y) %>%
       select(Symbol, everything())) %>% 
  bind_rows()

tbl
#> # A tibble: 2 × 7
#>   Symbol  Open  High   Low Close   Volume Adjusted
#>   <chr>  <dbl> <dbl> <dbl> <dbl>    <dbl>    <dbl>
#> 1 ATVI    78.2  80.8  77.3  79.6 12096300     79.6
#> 2 TTWO   153.  156   152.  154.   2893500    154.

write_excel_csv(tbl, 'stock_info.csv')

Created on 2021-09-13 by the reprex package (v2.0.0)

Upvotes: 1

Related Questions