Ajuste de calendario para series de tiempo en Excel

Scene 1:

Hello and welcome to the NumXL trading day effect tutorial. In this video will demonstrate the calendar function in NumXL and calculate the number of weekdays holidays and values of a trading effect variable for a monthly time series.

Let's compute the first and last state for a given month. Select the cell at C4, then in the formula bar enter EOMONTH function and click on the FX button.

Scene 2:

Using the function argument dialog, select the month you want. For the start date we can pick any day in the month. For the month let's move back one month.

Scene 3:

The EOMONTH function returns the end date of the preceding month. To get the beginning of the current month advance the date by one day. Now we have the first day of the month.

For the end of month date, select a cell and type EOMONTH in the formula bar. For the start date select the cell in B4. Enter 0 for the number of months to designate the current month.

Scene 4:

Now we have the first day and last day of the first month. To compute the length of a month subtract the last day from the first day and add one. Select the cells and copy the formula to the verse below it.

Scene 5:

To compute the number of workdays we'll use the NumXL's function, NxNetworkday. Click on the Fx button to launch functions argument dialog.

Scene 6:

Select the last day of the month as the end date. Select the first day of the month as the start date. Leave the holidays and the weekend variables to their defaults which are federal holidays and the western regions. Click OK when done.

Scene 7:

Copy the formula to the rows below it.

Now let's compute the number of Mondays, Tuesdays and other days in a month. To do so we'll compute the last Monday in a month and then use that day to look up the order of weekend day in a month. Click on the FX button to launch the dialog box.

Scene 8:

For the weekday arguments select the cell J2 and lock it for row movement. For the order select -1 for last. For the month use the month function to extract it from B4.

For the year argument do the same thing, but use the year function. Lock for column movement.

Scene 9:

Now use the return date. Look up its order using the NXWKday order function. The function returns the order of the weekday in the given month.

Scene 10:

Copy the formula to the right, then copy it to the cells below. Now let's compute the weekdays in the month. Select the weekdays, then copy the formula to the cells below.

Scene 11:

For the holidays columns, subtract the number of weekdays from the workdays then copy the formula to the rows below it.

Scene 12:

To compute the value for the first training day's variable, subtract the number of Mondays from the number of Sundays in the same month. Log the number of Sunday cells for column movement. Copy the formula to the right for the next variable. Go ahead and copy it all the way to the right. Now select the first row and copy the formulas to the rows below.

Scene 13:

That's it for now, in the next tutorial we'll examine the explanatory power of the values and the time series.

Scene 14:

That is it for now, thank you for watching!