Converting Daily data into Weekly data 3

[ADE6]

Hi,

Below I have a table showing various prices reached during the trading day of the Ftse 100 share index.A new row will be added to the DAILY table at 10pm after the end of each days trading and not before.

I would like to convert the daily data into a weekly time frame, showing the same fields but referring to the levels(Open,High,Low,Close) during the previous completed trading weeks only.The date in the WEEKLY table is the first working day of each week.




DAILY OPEN HIGH LOW CLOSE
06/10/2006 6,005 6,014 5,978 6,001
05/10/2006 5,967 6,017 5,967 6,005
04/10/2006 5,937 5,969 5,922 5,967
03/10/2006 5,958 5,958 5,897 5,937
02/10/2006 5,961 5,986 5,951 5,958
29/09/2006 5,971 6,003 5,950 5,961
28/09/2006 5,930 5,979 5,930 5,971
27/09/2006 5,874 5,942 5,873 5,930
26/09/2006 5,798 5,879 5,798 5,874
25/09/2006 5,822 5,847 5,775 5,798
22/09/2006 5,897 5,897 5,820 5,822
21/09/2006 5,866 5,898 5,849 5,897
20/09/2006 5,832 5,881 5,821 5,866


WEEKLY OPEN HIGH LOW CLOSE
02/10/2006 5,961 6,017 5,897 6,001
25/09/2006 5,822 6,003 5,775 5,961
18/09/2006 5,877 5,912 5,820 5,822
11/09/2006 5,879 5,944 5,820 5,877

So when the coming Monday the 9/10/2006 has completed trading it will be added as a row to the DAILY table but NOT to the WEEKLY table because the trading week has not finshed,so a new row will not be added to the weekly table until after 10 pm on the Friday of each week when all markets have closed for the week.

I'm not very good with time and date formulas, so can anybody advise me as how best to approach this problem and the type of formulas I will need.

I need this to run without manual intervention if that is possible


Really appreciate the help.

Ade

 

[DirkStruan]

This is the best answer I can provide without additional information on how the data is added to the table at the end of the day. As you want it to be automated, you're looking for some VB so this probably isn't the best forum.

I assume the daily data is in columns A to E

Use Edit menu/insert/name/define and define the names "Totrows", "MaxWeek" and "MinWeek" which are defined in the "Refers to" box as "=COUNTA($A:$A)", "=MAX($F:$F)" and "=MIN($F:$F)" respectively.

Under Tools/Macro/Visual Basic Editor add a new module and paste the following code which enters all the necessary functions and titles for you:

Sub CreateWeeklyTable()

totrows = Evaluate(Names("Totrows").Value)

Range("H1").CurrentRegion.Clear

Range("F1") = "Week"
Range("H1") = "Week"
Range("I1") = "Days in Week"
Range("J1") = "First row"
Range("K1") = "Last row"
Range("L1") = "Include"
Range("M1") = "Date"
Range("N1") = "Open"
Range("O1") = "High"
Range("P1") = "Low"
Range("Q1") = "Close"

For r = 2 To totrows
    Cells(r, 6).FormulaR1C1 = "=WEEKNUM(RC[-5],1)"
Next r

maxweek = Evaluate(Names("MaxWeek").Value)
minweek = Evaluate(Names("MinWeek").Value)

For r2 = 2 To maxweek - minweek + 2
    Cells(r2, 8).Value = maxweek + 2 - r2
    Cells(r2, 9).FormulaR1C1 = "=COUNTIF(C[-3],""=""&RC[-1])"
    Cells(r2, 10).FormulaR1C1 = "=MATCH(RC[-2],C[-4],0)"
    Cells(r2, 11).FormulaR1C1 = "=RC10+RC9-1"
    Cells(r2, 12).FormulaR1C1 = "=IF(WEEKDAY(INDEX(C[-11],MATCH(RC8,C[-6],0)),2)=5,""Y"",""N"")"
    If Cells(r2, 12).Value = "Y" Then
        Cells(r2, 13).FormulaR1C1 = "=INDEX(C[-12],RC[-3])"
        Cells(r2, 13).NumberFormat = "dd/mm/yyyy"
        Cells(r2, 14).FormulaR1C1 = "=INDEX(C[-12],RC[-3])"
        Cells(r2, 15).FormulaR1C1 = "=MAX(OFFSET(R1C3,RC10-1,0):OFFSET(R1C3,RC11-1,0))"
        Cells(r2, 16).FormulaR1C1 = "=MIN(OFFSET(R1C4,RC10-1,0):OFFSET(R1C4,RC11-1,0))"
        Cells(r2, 17).FormulaR1C1 = "=INDEX(C[-12],RC10)"
    End If
Next r2
End Sub

The next problem is how you trigger the above macro - you'll probably want to use it from a sheet event but additional details on how the data is added would make this easier to tackle.




Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[MunkyGod]

I am assuming this is in excel? If not, please clarify. If you can send me an example with dummy data should be able to sort it out for you and send you an example.

Basically, you need to have your weekly as a pivot from the daily data. Use a =WEEKDAY in a hidden column by the end of the data on the daily sheet.

Then have a hidden sheet which equals all the cells in you daily and stick an autofilter on the WEEKDAY column for the end of the week day, then pivot off this and this will automatically give you your weekly data. Turn on Auto-refresh and no intervention is required. If thismakes no sense let me know and i'll post a step by step example.

Munky

 

[ADE6]

Hi,

Firstly, I would just like to thank both Dirk and MunkyGod for their efforts,I really appreciate it.

Yes, I am using Excel and unfortunately my knowledge is limited.


The Daily data table below is actual data for the Ftse 100.

The more I think about this the more complex it becomes, whatever system I use to derive the weekly data it has to be flexible enough to take account of the fact that some weeks there may not be trading on certain days , for example there may be no data for a Friday due to a Public Holiday in that country. That was maybe why I thought a cut off time on Friday might be the best way to account for that, not really sure.

I also just realised that when we start a new calendar year that the first few trading days could(if Thursday,Friday,etc) still be classed as the same working week, not very clear how Excel would deal with this if you assigned every week a number somewhere in the calculation.





DAILY OPEN HIGH LOW CLOSE
06/10/06 6,005 6,014 5,978 6,001
05/10/06 5,967 6,017 5,967 6,005
04/10/06 5,937 5,969 5,922 5,967
03/10/06 5,958 5,958 5,897 5,937
02/10/06 5,961 5,986 5,951 5,958
29/09/06 5,971 6,003 5,950 5,961
28/09/06 5,930 5,979 5,930 5,971
27/09/06 5,874 5,942 5,873 5,930
26/09/06 5,798 5,879 5,798 5,874
25/09/06 5,822 5,847 5,775 5,798
22/09/06 5,897 5,897 5,820 5,822
21/09/06 5,866 5,898 5,849 5,897
20/09/06 5,832 5,881 5,821 5,866
19/09/06 5,890 5,897 5,832 5,832
18/09/06 5,877 5,912 5,870 5,890
15/09/06 5,877 5,899 5,865 5,877
14/09/06 5,892 5,944 5,869 5,877
13/09/06 5,896 5,913 5,875 5,892
12/09/06 5,851 5,897 5,824 5,896
11/09/06 5,879 5,879 5,820 5,851
08/09/06 5,858 5,899 5,858 5,879
07/09/06 5,929 5,929 5,853 5,858
06/09/06 5,982 5,982 5,926 5,929
05/09/06 5,987 5,991 5,956 5,982
04/09/06 5,949 5,987 5,948 5,987
01/09/06 5,906 5,968 5,906 5,949
31/08/06 5,929 5,937 5,895 5,906
30/08/06 5,888 5,945 5,888 5,929
29/08/06 5,879 5,921 5,879 5,888
25/08/06 5,869 5,894 5,859 5,879
24/08/06 5,860 5,892 5,833 5,869
23/08/06 5,903 5,906 5,854 5,860
22/08/06 5,915 5,939 5,878 5,903
21/08/06 5,903 5,937 5,884 5,915
18/08/06 5,900 5,933 5,900 5,903
17/08/06 5,897 5,915 5,889 5,900
16/08/06 5,898 5,903 5,849 5,897
15/08/06 5,871 5,903 5,845 5,898
14/08/06 5,820 5,871 5,820 5,871
11/08/06 5,823 5,848 5,797 5,820
10/08/06 5,861 5,861 5,753 5,823
09/08/06 5,818 5,866 5,778 5,861
08/08/06 5,829 5,867 5,818 5,818
07/08/06 5,889 5,889 5,821 5,829
04/08/06 5,838 5,893 5,837 5,889
03/08/06 5,932 5,941 5,827 5,838
02/08/06 5,881 5,932 5,881 5,932
01/08/06 5,928 5,950 5,867 5,881
31/07/06 5,975 5,977 5,928 5,928
28/07/06 5,930 5,983 5,905 5,975
27/07/06 5,877 5,937 5,877 5,930
26/07/06 5,851 5,879 5,851 5,877
25/07/06 5,834 5,873 5,826 5,851
24/07/06 5,720 5,835 5,720 5,834
21/07/06 5,771 5,771 5,701 5,720
20/07/06 5,778 5,820 5,756 5,771
19/07/06 5,682 5,785 5,681 5,778
18/07/06 5,701 5,713 5,658 5,682
17/07/06 5,708 5,721 5,655 5,701
14/07/06 5,765 5,765 5,708 5,708
13/07/06 5,861 5,861 5,752 5,765
12/07/06 5,857 5,899 5,843 5,861
11/07/06 5,897 5,897 5,844 5,857
10/07/06 5,889 5,901 5,857 5,897
07/07/06 5,890 5,909 5,858 5,889
06/07/06 5,827 5,897 5,827 5,890


The weekly data shows the actual results that should be produced by whatever method is used to solve the problem.

The weekly date refers to the date of the first trading day of each week,it may not always be a Monday if there are holidays or other events.


The Open price would be the open of the fist trading day of that week( 02/10/06)

The High is the priced reached during the previous workweek,for the first row it was on the 05/10/06.

The Low is the lowest point reached during the last completed workweek, reached on Tuesday 03/10/06.

Close would be the Close on the last day trading day during the workweek.

The weekday table would now not be updated until after next weeks trading has been completed.




WEEKLY OPEN HIGH LOW CLOSE
02/10/06 5,961 6,017 5,897 6,001
25/09/06 5,822 6,003 5,775 5,961
18/09/06 5,877 5,912 5,820 5,822
11/09/06 5,879 5,944 5,820 5,877
04/09/06 5,949 5,991 5,853 5,879
29/08/06 5,879 5,968 5,879 5,949
21/08/06 5,903 5,939 5,833 5,879
14/08/06 5,820 5,933 5,820 5,903
07/08/06 5,889 5,889 5,753 5,820
31/07/06 5,975 5,977 5,827 5,889
24/07/06 5,720 5,983 5,720 5,975
17/07/06 5,708 5,820 5,655 5,720
10/07/06 5,889 5,901 5,708 5,708
03/07/06 5,833 5,909 5,816 5,889
26/06/06 5,692 5,866 5,634 5,833
19/06/06 5,597 5,737 5,585 5,692
12/06/06 5,655 5,702 5,467 5,597
05/06/06 5,765 5,790 5,563 5,655
30/05/06 5,791 5,803 5,592 5,765
22/05/06 5,657 5,791 5,511 5,791
15/05/06 5,912 5,912 5,619 5,657
08/05/06 6,092 6,134 5,912 5,912
02/05/06 6,023 6,100 6,001 6,092
24/04/06 6,133 6,137 6,023 6,023
18/04/06 6,029 6,137 6,026 6,133
10/04/06 6,026 6,093 5,975 6,029
03/04/06 5,965 6,074 5,965 6,026
27/03/06 6,036 6,047 5,927 5,965

 

[ADE6]

The ultimate aim of this exercise is to manipulate the Daily data that comes in, as in the table in the above post to chrurn out data tables with the fields

DAte, Open High ,Low ,Close for not just the weekly time frame but also for the monthly and yearly time frames.

Another priority is to make it 100% automated, either by the use of macros or formulas or both.

Any help that I receive enabling me to get to that point is greatly appreciated.

Many thanks

Ade

 

[ADE6]

Dirk's solution appears to have done the trick, thank you.

All I need to do now is modify it to produce the same data tables for monthly and yearly time frames.

 

[SkipVought]

Hi,

The PivotTable Wizard is an invaluable tool for aggregating data. You can GROUP the dates by Week, Month, Quarters, years...

Great tool -- Data/Pivot Tables & Charts.

From you table of daily transactions you could generate a Weekly Report in about 15 seconds.

Skip,
[sub]
[red][/red]
[/sub]