How can I make a loop with tickers created in Excel in R?

Question

I am trying to make a loop in R with tickers I have created in Excel. I am trying to collect stock data from Yahoo finance, and the idea is that R is going to read the name of the stock from the Excel file, and then collect it from Yahoo Finance and create a graph.

In the data frame "Stocks" there are 10 different stocks listed in different columns, and I would like to run a loop so that I can get 10 different graphs. Here is the formula I have used to create a graph out of the first stock in the dataset.

 `Stocks %>%
ggplot(aes(x=Date, y = NOVO.B.CO.Close)) + 
geom_line(col = "darkgreen") + 
geom_point(col = "darkgreen") +
theme_gray() + 
ggtitle("Novo Nordiske B") `

Plenty of questions answer this. For importing data [from excel](https://stackoverflow.com/questions/37938265/importing-excel-table-into-database). Downloading the data directly from [yahoo in R](https://stackoverflow.com/questions/48568159/get-data-from-yahoo-finance-to-r). For plotting multiple securities create a single `data.frame` with `ticker` `close` etc and use `ticker` for colouring `ggplot(data = df, aes(x = time, y = close, col = ticker)) + geom_line() + geom_point() + theme_gray()`. — Oliver, Aug 25 '20 at 12:49
Not sure if I understood your response here. I already have imported the excel sheet, and all the data I need is under the data frame "Stocks". I know how to download data from Yahoo, as I have been able to create a graph with the closing price for the last seven days. The problem is just to create a for-loop, so I don't need to put in the same formula 10 times. — Silje Bue, Aug 25 '20 at 13:05
Ah, I misunderstood your question somewhat. It is often beneficial to have the data in `long` format for ggplot, as using `col = column` in `aes` will automatically handle colouring or using `facet_wrap`/`facet_grid` can help splitting the plot into sub-plots. For automating process of individual plots, you may get some inspiration from the question and my answer to [this question](https://stackoverflow.com/a/63455780/10782538), which gives a somewhat simple solution. — Oliver, Aug 25 '20 at 13:14
For a single plot: `base_plot <- ggplot(Stocks, aes(x = Date)) + theme_gray(); for(i in seq_along(tickers))base_plot <- base_plot + geom_line(aes_string(x = tickers[i]), col = colors[i]) + geom_point(aes_string(x = tickers[i]), col = colors[i])` where `ticker` is the column names and `colors` is a vector of colors. For multiple plots, create a list and store each plot into a vector and use my answer in the above linked question. — Oliver, Aug 25 '20 at 13:16

score 0 · Answer 1 · answered Aug 25 '20 at 13:44

Wrapping my comments into a proper answer. It is not clear whether you want to make this a single plot (using facet_wrap) or multiple plots combined into a single window. When using ggplot2 it is beneficial to have a single data.frame in long format, as one can let ggplot handle all of the colouring and grouping based on a grouping column similar to the example below

library(ggplot2)
data(mtcars)
ggplot(mtcars, aes(x = mpg, y = hp, col = factor(cyl))) + 
  geom_point() + 
  geom_smooth() + 
  labs(col = 'Nmb. Cylinder')

From here the guide gives names for each colour, and scale_*_(manual/discrete/continuous) can be used to change specific colour palettes (eg scale_colour_discrete can be used to change the palette for factors).

When it comes to combining ggplots the patchwork package provides the simple interface. If we assume you have a vector tickers, titles and colors respectively, we can create a list of plots and combine them using simply addition (+).

library(purrr)
plots <- vector('list', n <- length(tickers))
base <- ggplot(Stocks, aes(x = Date)) + 
  theme_gray() 
for(i in seq_len(n)){
  plots[[i]] <- base + 
    geom_point(aes_string(y = tickers[i]), col = colors[i]) 
    geom_line(aes_string(y = tickers[i]), col = colors[i]) 
    ggtitle(titles[i])
}
reduce(plots, `+`)

However for stocks the first option is likely going to give a better result.

How can I make a loop with tickers created in Excel in R?

1 Answers1