Add a new Column in Result Table with economic return

General
1

Hello Community,

We are using the batchgetsymbols to dowload and list the data of S&P/TSX Composite index.
Now we want to add a new column with the economic return for each Ticker.
We saw that there is a function to calculate the Return:

calc.ret Function to calculate returns from a price and ticker vector
Description
Created so that a return column is added to a dataframe with prices in the long (tidy) format.
Usage
calc.ret(P, tickers = rep("ticker", length(P)), type.return = "arit")

But we already tried different ways to get these values. Without sucess.

Could you please help us to find the best way to get these values and add them in our result table? Thanks.

Our Dataset ("SPTXS300 Tickers input.csv")

Ticker
ARE.TO
AFN.TO
AEM.TO
AC.TO
ASR.TO
AGI.TO
AD.TO
AQN.TO
ATD-B.TO
ATD-A.TO
AP-UN.TO
ALA.TO
AIF.TO
APHA.TO
ARX.TO
ATZ.TO
AX-UN.TO
ACO-X.TO
ATA.TO
ACB.TO
BTO.TO
BAD.TO
BLDP.TO
BMO.TO
BNS.TO
ABX.TO
BHC.TO
BTE.TO
BCE.TO
BB.TO
BEI-UN.TO
BBD-B.TO
BBD-A.TO
BBDA.TO
BLX.TO
BAM-A.TO
BBU-UN.TO
BIP-UN.TO
BPY-UN.TO
BEP-UN.TO
DOO.TO
CAE.TO
CCO.TO
GOOS.TO
CAR-UN.TO
CM.TO

Our Code:

library(BatchGetSymbols)

rm(list = ls())

#X=read.csv(choose.files())
#tickers <- c('CIX.TO','CGX.TO','CCA.TO')

first.date <- Sys.Date() - 30
last.date <- Sys.Date()
freq.data <- 'daily'


df.tickers <- read.csv("C:\\Users\\Alfredj\\Documents\\PROJETS\\TradinG\\S&P_TSX (GSPTSE)\\SPTXS300 Tickers input.csv",header=T)
tickers <- df.tickers$Ticker

result <- BatchGetSymbols(tickers = tickers,
                          # src = 'yahoo',
                          first.date = first.date,
                          last.date = last.date,
                          freq.data = freq.data,
                          cache.folder = file.path(tempdir(),'BGS_Cache'))
                          #calc.ret(P, tickers = rep("ticker", length(P)), type.return = "arit")
print(result$df.control)


result <- as.data.frame(result$df.tickers)
#df.tickers$ret.closing.prices <- calc.ret(result$price.close,tickers,type.return = 'arit')
df.tickers$ret.closing.prices <- calc.ret(df.tickers$price.close,df.tickers$Ticker,type.return = 'arit')
my.l <- list(result$df.control ,df.tickers = df.tickers)

write.csv(result,"C:\\Users\\Alfredj\\Documents\\PROJETS\\TradinG\\S&P_TSX (GSPTSE)\\DATA_SPTSX300.csv",row.names = TRUE)

View(result)
View(df.tickers$ret.closing.prices)
2

Hi, and welcome!

Please see the FAQ: What's a reproducible example (`reprex`) and how do I do one? Using a reprex, complete with representative data will attract quicker and more answers.

The code is not enough; the data is essential. Doesn't have to be the actual tickers you're using or more than just a few. Without the data the trade is a DK

1 Like
3

Merci bcp Technocrat, I just updated the dataset in my post. Please let me know if it's enough now?

Thanks,

Alfredo

4

Je vous en prie.

Thanks for the data, and I can put it together with the code and take a look.

Here's how the data should look (I did it easily since it's so simple)

ticker <- c("ARE.TO", "AFN.TO", "AEM.TO", "AC.TO", "ASR.TO", "AGI.TO", "AD.TO", "AQN.TO", "ATD-B.TO", "ATD-A.TO", "AP-UN.TO", "ALA.TO", "AIF.TO", "APHA.TO", "ARX.TO", "ATZ.TO", "AX-UN.TO", "ACO-X.TO", "ATA.TO", "ACB.TO", "BTO.TO", "BAD.TO", "BLDP.TO", "BMO.TO", "BNS.TO", "ABX.TO", "BHC.TO", "BTE.TO", "BCE.TO", "BB.TO", "BEI-UN.TO", "BBD-B.TO", "BBD-A.TO", "BBDA.TO", "BLX.TO", "BAM-A.TO", "BBU-UN.TO", "BIP-UN.TO", "BPY-UN.TO", "BEP-UN.TO", "DOO.TO", "CAE.TO", "CCO.TO", "GOOS.TO", "CAR-UN.TO", "CM.TO")

I take a look at it. Edit the OP to include the data this way, so others might weigh in too?
5

Dope slap me. I overlooked the data in the comment. Too quick on the trigger.

#tickers <- c('CIX.TO','CGX.TO','CCA.TO')

library(BatchGetSymbols)
#> Loading required package: rvest
#> Loading required package: xml2
#> Loading required package: dplyr
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
#> 
suppressPackageStartupMessages(library(dplyr))
suppressPackageStartupMessages(library(tibble)) 
suppressPackageStartupMessages(library(tidyr)) 
# https://forum.posit.co/t/add-a-new-column-in-result-table-with-economic-return/60338/3
tickers <-
  c(
    "ARE.TO",
    "AFN.TO",
    "AEM.TO",
    "AC.TO",
    "ASR.TO",
    "AGI.TO",
    "AD.TO",
    "AQN.TO",
    "ATD-B.TO",
    "ATD-A.TO",
    "AP-UN.TO",
    "ALA.TO",
    "AIF.TO",
    "APHA.TO",
    "ARX.TO",
    "ATZ.TO",
    "AX-UN.TO",
    "ACO-X.TO",
    "ATA.TO",
    "ACB.TO",
    "BTO.TO",
    "BAD.TO",
    "BLDP.TO",
    "BMO.TO",
    "BNS.TO",
    "ABX.TO",
    "BHC.TO",
    "BTE.TO",
    "BCE.TO",
    "BB.TO",
    "BEI-UN.TO",
    "BBD-B.TO",
    "BBD-A.TO",
    "BBDA.TO",
    "BLX.TO",
    "BAM-A.TO",
    "BBU-UN.TO",
    "BIP-UN.TO",
    "BPY-UN.TO",
    "BEP-UN.TO",
    "DOO.TO",
    "CAE.TO",
    "CCO.TO",
    "GOOS.TO",
    "CAR-UN.TO",
    "CM.TO"
  )

first.date <- Sys.Date() - 30
last.date <- Sys.Date()
freq.data <- 'daily'

result <- BatchGetSymbols(
  tickers = tickers,
  # src = 'yahoo', the d  efault
  first.date = first.date,
  last.date = last.date,
  freq.data = freq.data,
  cache.folder = file.path(tempdir(), 'BGS_Cache')
)
#> 
#> Running BatchGetSymbols for:
#> 
#>    tickers =ARE.TO, AFN.TO, AEM.TO, AC.TO, ASR.TO, AGI.TO, AD.TO, AQN.TO, ATD-B.TO, ATD-A.TO, AP-UN.TO, ALA.TO, AIF.TO, APHA.TO, ARX.TO, ATZ.TO, AX-UN.TO, ACO-X.TO, ATA.TO, ACB.TO, BTO.TO, BAD.TO, BLDP.TO, BMO.TO, BNS.TO, ABX.TO, BHC.TO, BTE.TO, BCE.TO, BB.TO, BEI-UN.TO, BBD-B.TO, BBD-A.TO, BBDA.TO, BLX.TO, BAM-A.TO, BBU-UN.TO, BIP-UN.TO, BPY-UN.TO, BEP-UN.TO, DOO.TO, CAE.TO, CCO.TO, GOOS.TO, CAR-UN.TO, CM.TO
#>    Downloading data for benchmark ticker
#> ^GSPC | yahoo (1|1) | Not Cached | Saving cache
#> ARE.TO | yahoo (1|46) | Not Cached | Saving cache - Got 100% of valid prices | Nice!
#> AFN.TO | yahoo (2|46) | Not Cached | Saving cache - Got 100% of valid prices | You got it!
#> AEM.TO | yahoo (3|46) | Not Cached | Saving cache - Got 100% of valid prices | Well done!
#> AC.TO | yahoo (4|46) | Not Cached | Saving cache - Got 100% of valid prices | Looking good!
#> ASR.TO | yahoo (5|46) | Not Cached | Saving cache - Got 100% of valid prices | Looking good!
#> AGI.TO | yahoo (6|46) | Not Cached | Saving cache - Got 100% of valid prices | You got it!
#> AD.TO | yahoo (7|46) | Not Cached | Saving cache - Got 100% of valid prices | Feels good!
#> AQN.TO | yahoo (8|46) | Not Cached | Saving cache - Got 100% of valid prices | OK!
#> ATD-B.TO | yahoo (9|46) | Not Cached | Saving cache - Got 100% of valid prices | You got it!
#> ATD-A.TO | yahoo (10|46) | Not Cached | Saving cache - Got 100% of valid prices | Mais contente que cusco de cozinheira!
#> AP-UN.TO | yahoo (11|46) | Not Cached | Saving cache - Got 100% of valid prices | Well done!
#> ALA.TO | yahoo (12|46) | Not Cached | Saving cache - Got 100% of valid prices | Nice!
#> AIF.TO | yahoo (13|46) | Not Cached | Saving cache - Got 100% of valid prices | Nice!
#> APHA.TO | yahoo (14|46) | Not Cached | Saving cache - Got 100% of valid prices | Got it!
#> ARX.TO | yahoo (15|46) | Not Cached | Saving cache - Got 100% of valid prices | You got it!
#> ATZ.TO | yahoo (16|46) | Not Cached | Saving cache - Got 100% of valid prices | Feels good!
#> AX-UN.TO | yahoo (17|46) | Not Cached | Saving cache - Got 100% of valid prices | Got it!
#> ACO-X.TO | yahoo (18|46) | Not Cached | Saving cache - Got 100% of valid prices | Looking good!
#> ATA.TO | yahoo (19|46) | Not Cached | Saving cache - Got 100% of valid prices | Good stuff!
#> ACB.TO | yahoo (20|46) | Not Cached | Saving cache - Got 100% of valid prices | Youre doing good!
#> BTO.TO | yahoo (21|46) | Not Cached | Saving cache - Got 100% of valid prices | Got it!
#> BAD.TO | yahoo (22|46) | Not Cached | Saving cache - Got 100% of valid prices | Good job!
#> BLDP.TO | yahoo (23|46) | Not Cached | Saving cache - Got 100% of valid prices | OK!
#> BMO.TO | yahoo (24|46) | Not Cached | Saving cache - Got 100% of valid prices | Good job!
#> BNS.TO | yahoo (25|46) | Not Cached | Saving cache - Got 100% of valid prices | Good stuff!
#> ABX.TO | yahoo (26|46) | Not Cached | Saving cache - Got 100% of valid prices | Good job!
#> BHC.TO | yahoo (27|46) | Not Cached | Saving cache - Got 100% of valid prices | Good job!
#> BTE.TO | yahoo (28|46) | Not Cached | Saving cache - Got 100% of valid prices | Good job!
#> BCE.TO | yahoo (29|46) | Not Cached | Saving cache - Got 100% of valid prices | OK!
#> BB.TO | yahoo (30|46) | Not Cached | Saving cache - Got 100% of valid prices | Well done!
#> BEI-UN.TO | yahoo (31|46) | Not Cached | Saving cache - Got 100% of valid prices | Nice!
#> BBD-B.TO | yahoo (32|46) | Not Cached | Saving cache - Got 100% of valid prices | Good job!
#> BBD-A.TO | yahoo (33|46) | Not Cached | Saving cache - Got 100% of valid prices | Good job!
#> BBDA.TO | yahoo (34|46) | Not Cached - Error in download..
#> BLX.TO | yahoo (35|46) | Not Cached | Saving cache - Got 100% of valid prices | Boa!
#> BAM-A.TO | yahoo (36|46) | Not Cached | Saving cache - Got 100% of valid prices | Nice!
#> BBU-UN.TO | yahoo (37|46) | Not Cached | Saving cache - Got 100% of valid prices | OK!
#> BIP-UN.TO | yahoo (38|46) | Not Cached | Saving cache - Got 100% of valid prices | Good stuff!
#> BPY-UN.TO | yahoo (39|46) | Not Cached | Saving cache - Got 100% of valid prices | Got it!
#> BEP-UN.TO | yahoo (40|46) | Not Cached | Saving cache - Got 100% of valid prices | Nice!
#> DOO.TO | yahoo (41|46) | Not Cached | Saving cache - Got 100% of valid prices | Good stuff!
#> CAE.TO | yahoo (42|46) | Not Cached | Saving cache - Got 100% of valid prices | Feels good!
#> CCO.TO | yahoo (43|46) | Not Cached | Saving cache - Got 100% of valid prices | Looking good!
#> GOOS.TO | yahoo (44|46) | Not Cached | Saving cache - Got 100% of valid prices | Looking good!
#> CAR-UN.TO | yahoo (45|46) | Not Cached | Saving cache - Got 100% of valid prices | Youre doing good!
#> CM.TO | yahoo (46|46) | Not Cached | Saving cache - Got 100% of valid prices | Feels good!

str(result) # turns a list of two, only second is of interest
#> List of 2
#>  $ df.control: tibble [45 × 6] (S3: tbl_df/tbl/data.frame)
#>   ..$ ticker              : chr [1:45] "ARE.TO" "AFN.TO" "AEM.TO" "AC.TO" ...
#>   ..$ src                 : chr [1:45] "yahoo" "yahoo" "yahoo" "yahoo" ...
#>   ..$ download.status     : chr [1:45] "OK" "OK" "OK" "OK" ...
#>   ..$ total.obs           : int [1:45] 22 22 22 22 22 22 22 22 22 22 ...
#>   ..$ perc.benchmark.dates: num [1:45] 1 1 1 1 1 1 1 1 1 1 ...
#>   ..$ threshold.decision  : chr [1:45] "KEEP" "KEEP" "KEEP" "KEEP" ...
#>  $ df.tickers:'data.frame':  990 obs. of  10 variables:
#>   ..$ price.open         : num [1:990] 16 16.8 16.3 15 15.1 ...
#>   ..$ price.high         : num [1:990] 17 17.1 16.5 15.2 15.6 ...
#>   ..$ price.low          : num [1:990] 16 16.5 15.8 14.3 14.4 ...
#>   ..$ price.close        : num [1:990] 16.3 16.7 15.8 14.4 15.5 ...
#>   ..$ volume             : num [1:990] 755700 682800 408700 658000 689700 ...
#>   ..$ price.adjusted     : num [1:990] 16.1 16.5 15.6 14.2 15.3 ...
#>   ..$ ref.date           : Date[1:990], format: "2020-03-09" "2020-03-10" ...
#>   ..$ ticker             : chr [1:990] "ARE.TO" "ARE.TO" "ARE.TO" "ARE.TO" ...
#>   ..$ ret.adjusted.prices: num [1:990] NA 0.0233 -0.051 -0.0923 0.0822 ...
#>   ..$ ret.closing.prices : num [1:990] NA 0.0233 -0.051 -0.0923 0.0822 ...

result <- as.data.frame(result[2])

# variables don't exist in the `tickers` provided
# df.tickers$ret.closing.prices <- calc.ret(result$price.close,tickers,type.return = 'arit')
# df.tickers$ret.closing.prices <- calc.ret(df.tickers$price.close, df.tickers$Ticker, type.return = 'arit')
# my.l <- list(result$df.control , df.tickers = df.tickers)


# use write.csv for export
# write.csv(result,"C:\\Users\\Alfredj\\Documents\\PROJETS\\TradinG\\S&P_TSX (GSPTSE)\\DATA_SPTSX300.csv",row.names = TRUE)
# use RDS for between R sessions
# saveRDS(result, file = "result.RDS") outside of working directory use full path name if not a subdirectory

# using `result`
# save original column names, replace with shortcuts for ease of typing
# restore original with colnames(result) <- original_header (after adjusting for any additonal columns by subsetting)
colnames(result) -> original_header
# create more convenient header
colnames(result) <-
  c(
    "open",
    "high",
    "low",
    "close",
    "vol",
    "adj_vol",
    "refdate",
    "ticker",
    "adj_ret",
    "closing_ret"
  )
# step one reorganization, change order of variables
result %>% select(ticker, refdate, everything()) -> result
# "tidy" by adding record number, allowing to treat each row as an observation
rownames_to_column(result,var = "recid") -> result
# re data frame
# as.data.frame(result) -> result
result %>% select(recid, everything()) -> result
str(result)
#> 'data.frame':    990 obs. of  11 variables:
#>  $ recid      : chr  "1" "2" "3" "4" ...
#>  $ ticker     : chr  "ARE.TO" "ARE.TO" "ARE.TO" "ARE.TO" ...
#>  $ refdate    : Date, format: "2020-03-09" "2020-03-10" ...
#>  $ open       : num  16 16.8 16.3 15 15.1 ...
#>  $ high       : num  17 17.1 16.5 15.2 15.6 ...
#>  $ low        : num  16 16.5 15.8 14.3 14.4 ...
#>  $ close      : num  16.3 16.7 15.8 14.4 15.5 ...
#>  $ vol        : num  755700 682800 408700 658000 689700 ...
#>  $ adj_vol    : num  16.1 16.5 15.6 14.2 15.3 ...
#>  $ adj_ret    : num  NA 0.0233 -0.051 -0.0923 0.0822 ...
#>  $ closing_ret: num  NA 0.0233 -0.051 -0.0923 0.0822 ...
calc.ret
#> function (P, tickers = rep("ticker", length(P)), type.return = "arit") 
#> {
#>     my.length <- length(P)
#>     ret <- switch(type.return, arit = P/dplyr::lag(P) - 1, log = log(P/dplyr::lag(P)))
#>     idx <- (tickers != dplyr::lag(tickers))
#>     ret[idx] <- NA
#>     return(ret)
#> }
#> <bytecode: 0x7fdc24f644b0>
#> <environment: namespace:BatchGetSymbols>

# calc.net requires a vector of prices, which requires creating a collection
# of objects, each representing a vector of prices of each ticker separately
# so does doing it any other way, so some way is needed to do it for a batch of
# tickers within a single data frame; I'm turning to that. Function is only deigned for one vector at a time

Created on 2020-04-08 by the reprex package (v0.3.0)

1 Like
6

Hello Technocrat,

Merci bcp :slight_smile: it works.

1 Like
7

Great. Please let others know by marking how you fixed my partial answer as the solution for the benefit of those to follow.

8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.