Aggregating Weekly Financial Data into Monthly Averages
Summary
The document addresses converting a weekly financial risk index into monthly observations by averaging the values assigned to each month. It presents several approaches in R: grouping observations by calendar month with a base-language aggregation function, or using time-series tools that identify month-end positions and apply a function over each period. One response also suggests obtaining data directly at a monthly frequency through a data provider, when that option is available.
A worked example groups dates by year and month, calculates each group’s mean, and shows the resulting monthly values for the sample observations. The advice assumes calendar-month averages are the desired output; it does not compare this with taking the last weekly observation, weighting by elapsed time, or handling missing weeks. It also does not provide a Julia-specific solution, despite the question mentioning Julia.
Key ideas
- Group weekly observations by calendar year and month to compute monthly means.
- Time-series endpoint tools can identify the observations that bound each month for period-based aggregation.
- Base-language grouping and aggregation can produce the same kind of monthly summary.
- Monthly averages differ from month-end values, and the document does not compare the alternatives.
- No Julia implementation is supplied.
Tags
Full text
# How to convert weekly data to monthly in r (or in Julia) # How to convert weekly data to monthly in r (or in Julia) I have weekly series on financial risk index data as follows: DATE NFCIRISK 1/8/1971 0.58 1/15/1971 0.61 ......through 10/6/2017 -0.88 10/13/2017 -0.89 10/20/2017 -0.89 10/27/2017 -0.89 I want to convert them into a monthly(average of four weeks) series and I tried to use following in r but didnt work. library(xlsx) library(xts) month.end <- endpoints(MyData, on = "months") monthly <- period.apply(MyData, INDEX = month.end, FUN = mean) head(monthly) Could anyone please help me if any other ways I can try either in r or in Julia. Thanks and appreciating your response. ## Answer by Ganesh S (score 1) https://quant.stackexchange.com/a/36835 You can try following : Use "Quandl" package in R. Which allows you to download data for Monthly, Quarterly, Weekly, Daily directly using single argument. It also provides the Index data. Hope this will help you!! ## Answer by gene_clipper (score 1) https://quant.stackexchange.com/a/36836 See the endpoints function in R. > It returns a numeric vector corresponding to the last observation in each period specified by on, with a zero added to the beginning of the vector, and the index of the last raster in x at the end. Valid values for the argument on include: “us” (microseconds), “microseconds”, “ms” (milliseconds), “milliseconds”, “secs” (seconds), “seconds”, “mins” (minutes), “minutes”, “hours”, “days”, “weeks”, “months”, “quarters”, and “years”. ## Answer by Enrico Schumann (score 0) https://quant.stackexchange.com/a/36847 Such computations can be handled by `tapply`, which is in R base. Suppose your data is stored in a dataframe `MyData`, first column the timestamps, second column the values: ``` MyData <- read.table(text= "DATE NFCIRISK 01/8/1971 0.58 01/15/1971 0.61 10/6/2017 -0.88 10/13/2017 -0.89 10/20/2017 -0.89 10/27/2017 -0.89", sep = " ", stringsAsFactors = FALSE, header = TRUE) MyData[[1]] <- as.Date(MyData[[1]], "%m/%d/%Y") MyData ## DATE NFCIRISK ## 1 1971-01-08 0.58 ## 2 1971-01-15 0.61 ## 3 2017-10-06 -0.88 ## 4 2017-10-13 -0.89 ## 5 2017-10-20 -0.89 ## 6 2017-10-27 -0.89 ``` Then you can simply write: ``` tapply(MyData[[2]], format(MyData[[1]], "%Y-%m"), mean) ``` and get ``` 1971-01 2017-10 0.5950 -0.8875 ```
Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.