2 years ago
#63336
DBT
R: Calculate Monthly & Yearly Returns Based On Daily Price Data
Below is sample data that I have for two assets, I have 35 assets with varying start times to when data is available to be analyzed for. I had 35 separate .csv files and I have read them all into my environment and have combined them into 1 data frame in R that looks the the following sample data below: (Most assets have daily data, the start times to when the data is available is what varies, I subset the data to reduce the amount of records to provide an examples of what I am trying to achieve.
time close asset dchange month year
2020-05-22 0.8230766 Celo 198.2538837 5 2020
2020-05-23 2.4585544 Celo -19.85613825 5 2020
...
2020-05-31 1.4844532 Celo 6.02260537 5 2020
...
2020-12-01 1.9900000 Celo 5.98348954 12 2020
2020-12-31 2.0100000 Celo 1.00502513 12 2020
2019-09-01 0.08976233 HBAR -3.350752111 9 2019
2019-09-02 0.08675461 HBAR -19.85613825 9 2019
...
2019-09-30 0.03794833 HBAR 2.650509119 9 2019
...
2019-12-01 0.8092034 HBAR 5.98348954 12 2019
2019-12-31 1.2100000 HBAR 1.00502513 12 2019
"time" is the date of the the asset
"close" is the close price of the the asset
"asset" is the name of the asset
"dchange" is the daily percent change of the asset from current day to the previous day
"month" is the month in a numerical value
"year" is the year in a numerical value
I am trying to add two new columns that will calculate the following:
- Percentage change of the asset for the month & year combination (using earliest month data available and latest month data)
- Percentage change of the asset for the year (using earliest year data available to latest year data for the same year)
I am aiming for the following output:
time close asset dchange month year monthly change yearly change 4
2020-05-22 0.8230766 Celo 198.2538837 5 2020 80.35 144.20
2020-05-23 2.4585544 Celo -19.85613825 5 2020 80.35 144.20
...
2020-05-31 1.4844532 Celo 6.02260537 5 2020 80.35 144.20
...
2020-12-01 1.9900000 Celo 5.98348954 12 2020 5.08 144.20
2020-12-31 2.0100000 Celo 1.00502513 12 2020 5.08 144.20 144.20
2019-09-01 0.08976233 HBAR -3.350752111 9 2019 -57.23 1248
2019-09-02 0.08675461 HBAR -19.85613825 9 2019 -57.23 1248
...
2019-09-30 0.03794833 HBAR 2.650509119 9 2019 -57.23 1248
...
2019-12-01 0.8092034 HBAR 5.98348954 12 2019 49.52 1248
2019-12-31 1.2100000 HBAR 1.00502513 12 2019 49.52 1248 1248
I was able to conduct some research to understand I may be able to use monthlyReturn-function from quantmod, not sure if I should use this in conjunction with lapply? I would appreciate any help with trying to get the desired results.
r
time-series
return
currency
quantmod
0 Answers
Your Answer