Converting weekly price data into daily (5 days of the week) format

S

Sajjad Qureshi

I have a data file that looks like this.
26-Jan-68 1.02088 1.02088 1.02088 1.02088
2-Feb-68 1.01608 1.01608 1.01608 1.01608
9-Feb-68 1.01577 1.01577 1.01577 1.01577
16-Feb-68 1.01425 1.01425 1.01425 1.01425
..........

The current date fall on FRI. I want to add new rows with dates for
the following MoTuWeTh and copy the data from the last record into new
rows. In this example I will need to add 4 rows between 26-Jan-68 and
2-Feb-68, append dates that correspond to MoTuWeTh following 26-
Jan-68, and copy the data for 26-Jan-68 into these new rows. I need to
do this for all rows (which ends on current week--lots of rows to do
manually). Any help will be appreciated.
 
R

reza

I have a data file that looks like this.
26-Jan-68       1.02088 1.02088 1.02088 1.02088
2-Feb-68        1.01608 1.01608 1.01608 1.01608
9-Feb-68        1.01577 1.01577 1.01577 1.01577
16-Feb-68       1.01425 1.01425 1.01425 1.01425
.........

The current date fall on FRI. I want to add new rows with dates for
the following MoTuWeTh and copy the data from the last record into new
rows. In this example I will need to add 4 rows between 26-Jan-68 and
2-Feb-68, append dates that correspond to MoTuWeTh following 26-
Jan-68, and copy the data for 26-Jan-68 into these new rows. I need to
do this for all rows (which ends on current week--lots of rows to do
manually). Any help will be appreciated.
Click to expand...

here is a vba as your starting point:

Sub copydate()
Dim dt As Range, c As Range
Dim dtArray() As Variant
Dim i As Integer, j As Integer

Set dt = Range([a1], [a1].End(xlDown))
ReDim dtArray(1 To dt.Rows.Count * 5, 1 To 5)

i = 1
For Each c In dt
For j = 0 To 4
dtArray(i + j, 1) = c.Value + j
dtArray(i + j, 2) = c.Offset(0, 1)
dtArray(i + j, 3) = c.Offset(0, 2)
dtArray(i + j, 4) = c.Offset(0, 3)
dtArray(i + j, 5) = c.Offset(0, 4)
Next
i = i + 5
Next

Dim target As Range
Set target = [h1] ' change this to where you want the new data
to be copied
target.Resize(dt.Rows.Count * 5, 5) = dtArray
End Sub
 
D

Don Guillett

Or

Sub insertrowsifFRIDAY()
For i = Cells(Rows.Count, 1). _
End(xlUp).Row To 1 Step -1
If Format(Cells(i, 1), "ddd") = "Fri" Then
Rows(i).Copy
Rows(i + 1).Resize(4).Insert
Cells(i + 1, 1) = Cells(i, 1) + 3
Cells(i + 1, 1).AutoFill _
Destination:=Cells(i + 1, 1).Resize(4)
'MsgBox i
End If
Next i
End Sub


I have a data file that looks like this.
26-Jan-68       1.02088 1.02088 1.02088 1.02088
2-Feb-68        1.01608 1.01608 1.01608 1.01608
9-Feb-68        1.01577 1.01577 1.01577 1.01577
16-Feb-68       1.01425 1.01425 1.01425 1.01425
.........
Click to expand...
The current date fall on FRI. I want to add new rows with dates for
the following MoTuWeTh and copy the data from the last record into new
rows. In this example I will need to add 4 rows between 26-Jan-68 and
2-Feb-68, append dates that correspond to MoTuWeTh following 26-
Jan-68, and copy the data for 26-Jan-68 into these new rows. I need to
do this for all rows (which ends on current week--lots of rows to do
manually). Any help will be appreciated.
Click to expand...

here is a vba as your starting point:

Sub copydate()
    Dim dt As Range, c As Range
    Dim dtArray() As Variant
    Dim i As Integer, j As Integer

    Set dt = Range([a1], [a1].End(xlDown))
    ReDim dtArray(1 To dt.Rows.Count * 5, 1 To 5)

    i = 1
    For Each c In dt
        For j = 0 To 4
            dtArray(i + j, 1) = c.Value + j
            dtArray(i + j, 2) = c.Offset(0, 1)
            dtArray(i + j, 3) = c.Offset(0, 2)
            dtArray(i + j, 4) = c.Offset(0, 3)
            dtArray(i + j, 5) = c.Offset(0, 4)
        Next
        i = i + 5
    Next

    Dim target As Range
    Set target = [h1]   ' change this to where you want the new data
to be copied
    target.Resize(dt.Rows.Count * 5, 5) = dtArray
End Sub
Click to expand...
 
S

Sajjad Qureshi

I have a data file that looks like this.
26-Jan-68       1.02088 1.02088 1.02088 1.02088
2-Feb-68        1.01608 1.01608 1.01608 1.01608
9-Feb-68        1.01577 1.01577 1.01577 1.01577
16-Feb-68       1.01425 1.01425 1.01425 1.01425
.........
Click to expand...
The current date fall on FRI. I want to add new rows with dates for
the following MoTuWeTh and copy the data from the last record into new
rows. In this example I will need to add 4 rows between 26-Jan-68 and
2-Feb-68, append dates that correspond to MoTuWeTh following 26-
Jan-68, and copy the data for 26-Jan-68 into these new rows. I need to
do this for all rows (which ends on current week--lots of rows to do
manually). Any help will be appreciated.
Click to expand...

here is a vba as your starting point:

Sub copydate()
    Dim dt As Range, c As Range
    Dim dtArray() As Variant
    Dim i As Integer, j As Integer

    Set dt = Range([a1], [a1].End(xlDown))
    ReDim dtArray(1 To dt.Rows.Count * 5, 1 To 5)

    i = 1
    For Each c In dt
        For j = 0 To 4
            dtArray(i + j, 1) = c.Value + j
            dtArray(i + j, 2) = c.Offset(0, 1)
            dtArray(i + j, 3) = c.Offset(0, 2)
            dtArray(i + j, 4) = c.Offset(0, 3)
            dtArray(i + j, 5) = c.Offset(0, 4)
        Next
        i = i + 5
    Next

    Dim target As Range
    Set target = [h1]   ' change this to where you want the new data
to be copied
    target.Resize(dt.Rows.Count * 5, 5) = dtArray
End Sub
Click to expand...

It worked. Thanks in a million.
 
S

Sajjad Qureshi

Or

Sub insertrowsifFRIDAY()
For i = Cells(Rows.Count, 1). _
End(xlUp).Row To 1 Step -1
If Format(Cells(i, 1), "ddd") = "Fri" Then
Rows(i).Copy
Rows(i + 1).Resize(4).Insert
Cells(i + 1, 1) = Cells(i, 1) + 3
Cells(i + 1, 1).AutoFill _
 Destination:=Cells(i + 1, 1).Resize(4)
'MsgBox i
End If
Next i
End Sub

here is a vba as your starting point:
Click to expand...
Sub copydate()
    Dim dt As Range, c As Range
    Dim dtArray() As Variant
    Dim i As Integer, j As Integer
Click to expand...
    Set dt = Range([a1], [a1].End(xlDown))
    ReDim dtArray(1 To dt.Rows.Count * 5, 1 To 5)
Click to expand...
    i = 1
    For Each c In dt
        For j = 0 To 4
            dtArray(i + j, 1) = c.Value + j
            dtArray(i + j, 2) = c.Offset(0, 1)
            dtArray(i + j, 3) = c.Offset(0, 2)
            dtArray(i + j, 4) = c.Offset(0, 3)
            dtArray(i + j, 5) = c.Offset(0, 4)
        Next
        i = i + 5
    Next
Click to expand...
    Dim target As Range
    Set target = [h1]   ' change this to where you want the newdata
to be copied
    target.Resize(dt.Rows.Count * 5, 5) = dtArray
End Sub
Click to expand...
Click to expand...

Thanks for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel check if dates are under a range of week 4
Auto copy data rows between sheets depending on date entry 3
Converting daily data into weekly 1
Recording Historic Trend Data 1
Log data from timecard into table in separate worksheet 3
Capturing Trend Data over time 1
Adding days to the WORKDAY function 2
Weekly data allocated to months 2

Top