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

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Converting weekly price data into daily (5 days of the week) format (https://www.excelbanter.com/excel-programming/444217-converting-weekly-price-data-into-daily-5-days-week-format.html)

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

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.

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

On Feb 7, 9:19*pm, Sajjad Qureshi wrote:
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.

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

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

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

On Feb 7, 9:23*pm, reza wrote:
On Feb 7, 9:19*pm, Sajjad Qureshi wrote:

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.

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

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

On Feb 7, 10:23*pm, reza wrote:
On Feb 7, 9:19*pm,SajjadQureshi wrote:

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.

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

It worked. Thanks in a million.

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

On Feb 9, 8:53*am, Don Guillett wrote:
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

On Feb 7, 9:23*pm, reza wrote:

On Feb 7, 9:19*pm,SajjadQureshi wrote:

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.

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

Thanks for your help.