I need an Excel VBA Macro that will take input files with varying rows of data, calculate Exponentially Weighted Moving Averages for all periods, and output new files for each input file. Below is some basic instructions, I will provide example files and more details once the job is awarded.
Must be able to start within 24 hours of receiving additional info. Thanks for considering this project, look forward to working with you!
1) The run page will need a place to input:
a) The starting value of N
b) The ending value of N
c) The increment that N will increase by
d) Four separate cells to input up to four separate input file type designators
e) A run button
2) After I hit run I would like to be prompted to select what folder the input files will come from and what folder the output files will be saved in. These will be two separate locations.
3) The Exponentially Weighted Moving Average (EWMA)
a) X = A * D(t) + (1 - A) * D(t - 1)
b) A = the weight placed on the most recent period and the rate at which the weights decrease (decay).
c) A = 2 / (N + 1)
d) D(t) = current period
e) D(t – 1) = calculated EWMA for the prior period
f) N = number of periods that will be in the weighted average
i) Starting N will be defined by the user in the setup page
ii) Ending N will be defined by the user in the setup page
iii) Each iteration of N = last N + 1
a) The output files must have every business day from the first day that EWMA is calculated to the last ending date in the input file.
b) It will have to run every input file through every iteration of N and produce a separate output file for each input file/N combination.
c) Each output file name will be the input file name plus the two digit N of that iteration.