Data convertion

Hi Experts,

I need to convert short duration data to long time duration.
I have 1 min data of particular stock with Open - high - low close & volume.
I need to convert this 1 min data to longer duration data. say I have in I8:I13 - 15 min - 30 min - 60 min - 1 Day - 1 week - 1 month respectively so if I run code it creates sheets of this names if sheet is not existed. if existed then add data to existing sheets - if same data (Same date data) already there then over past on that data. There is Day Start Time 9:15 AM & Day End Time is 15:30 PM.

On Button click "Update"
Step 1 Find sheet names range I8:I13 - not found create - Found Update data
Step 2 Calculation of Data - say we have convert 1 min data to 15 min then - from 1 min data very 1 min Open = open of 15 min
                            high = highest high of 15 min from 1 min data which we are counting.
                            Low = lowest low of 15 min from 1 min data which we are counting.
                           Close = last 1 min data Close (15th min)
                           Volume = total volume of all 15 min from where we are counting.

I have one link which create Tick data to 1 min data see if that is useful.
Data Convert To Larger Time Frame - VBA

Thanks
Data.xlsx
LVL 8
Naresh PatelTraderAsked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
easier said then done !!!!
to do this we need to change the whole logic !!! I have fixed the highlighting of the range but for excluding everything not between 9:15 AM and 15:30 PM I prefer a new question !!

Sorry, you will see when the code is posted that it is a whole different approach. Here we jump by a certain number of rows that are the same all the time, (we introduced an exception for the last attempt if date in the block is different) however now with the time different it is a different ball game.

As will need to test now each line time which we are not doing and will need to loop by line and not by block so different logic.

Do not think that I am after points, I am after a solution. and what you simply posted here for 15, 30, 60, 1day, 1week, 1month seems for you so simple yet so not simple due to time intervals and unwanted portions.

gowflow
Data-Convert-V03.xlsm
0
 
clarkscottCommented:
Is there a question here?
It looks like an assignment request.

Scott C
0
 
gowflowCommented:
I would say looks more like a project
gowflow
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Naresh PatelTraderAuthor Commented:
Please suggest Experts, what you want me to do.Split question - Delete Question - Reframe Question.

Thanks
0
 
gowflowCommented:
Well common sense dictates that a question should hold so much. When it hold a lot then it become  too much and u should always keep in mind that ur questions are viewed by lots of people who may have similar problem or issue that you are asking if the threads are long then it become confusing to follow-up. Reason why thread should be kept concise and questions limited to specific issues not broad.
gowflow
0
 
gowflowCommented:
What did you decide ?
gowflow
0
 
Naresh PatelTraderAuthor Commented:
What did you decide ?
splitting

just want to convert 1 min data to 15 min data.

Thanks
0
 
gowflowCommented:
what do you mean convert 1 min to 15 minutes ? not clear
gowflow
0
 
Naresh PatelTraderAuthor Commented:
See attached I had manually created Sheet "15 Min" which is desire result.

Thanks
Data.xlsx
0
 
Naresh PatelTraderAuthor Commented:
but not whole calculation has done just 3 row data calculated.

Thanks
0
 
gowflowCommented:
Even in the example you get us confusingly mixed with the values isn't it like this:

1/1/2014 9:30       6355.05      6356.3      6355      6355.05      250
1/1/2014 9:45       6353.95      6354.3      6353.05      6354.3      131
1/1/2014 10:00      6353.55      6354      6353.1      6353.85      78

However you posted:
1/1/2014 9:30               6356.2      6359.95      6345.35      6355.05      7378
1/1/2014 9:45               6355.25      6356.45      6350.55      6354.3      2219
1/1/2014 10:00        6354.3      6355.4      6352.1      6353.85      2445

How you got your figures ? or mine correct ?
gowflow
0
 
Naresh PatelTraderAuthor Commented:
Pls give me 15 min
Thanks
0
 
Naresh PatelTraderAuthor Commented:
What we are doing is converting 1min Open High Low Close Volume to 15 Min Open High Low Close Volume. So Open for 1st 15 min is very first min Open of 1 min data ,High is  max value of  1st 1 min to 15th 1 min data.Low is same way min value of very 1st 1 min data to 15th 1 min data and close is close of 15th min 1 min data.Volume is sum of 1st 1 min to 15th 1 min data.

You can see formulas in sheet 15 min to understand.
pls ignore any typo mistake in this comment - if any.not in front of my machine.
Thanks

Thanks
0
 
gowflowCommented:
ok nevermind got it !!

just read your question again it is all there
gowlfow
0
 
gowflowCommented:
ok I don't know if the data produced is correct. Check it out by activating macros and run the macro called
Sub Convert_1to15()


Dim WS As Worksheet
Dim WSConv As Worksheet
Dim MaxRow As Long, MaxRowConv As Long, I As Long, J As Long
Dim StRow As Long, EndRow As Long

Set WS = ActiveSheet
MaxRow = WS.UsedRange.Rows.Count

'---> Check if Sheet 15 Min there
On Error Resume Next
Set WSConv = Sheets("15 Min")
If Err <> 0 Then
    Worksheets.Add after:=Worksheets(Worksheets.Count)
    Set WSConv = ActiveSheet
    WSConv.Name = "15 Min"
    WS.Range("1:1").Copy WSConv.Range("A1")
    WS.Range("A:A").Copy
    WSConv.Range("A1").PasteSpecial xlPasteFormats
    MaxRowConv = 2
Else
    MaxRowConv = WSConv.UsedRange.Rows.Count + 1
End If
On Error GoTo 0

'---> Start Process
For I = 2 To MaxRow Step 16
    WSConv.Cells(MaxRowConv, "A") = WS.Cells(I, "A")
    WSConv.Cells(MaxRowConv, "B") = WS.Cells(I, "B")
    WSConv.Cells(MaxRowConv, "C") = WS.Application.WorksheetFunction.Max(WS.Range("C" & I & ":C" & I + 15))
    WSConv.Cells(MaxRowConv, "D") = WS.Application.WorksheetFunction.Min(WS.Range("D" & I & ":D" & I + 15))
    WSConv.Cells(MaxRowConv, "E") = WS.Cells(I + 15, "E")
    WSConv.Cells(MaxRowConv, "F") = WS.Application.WorksheetFunction.Sum(WS.Range("F" & I & ":F" & I + 15))
    MaxRowConv = MaxRowConv + 1
Next I
End Sub

Open in new window


I personally don't think the data is correct as I assumed that every row is 1min where I saw that the last 2 of the day are
1/1/2014 15:29
1/1/2014 16:14

In my mind the data is all there however it maybe take the last of a certain day and combine it with the next day as I don't check the real time.

If this is needed then an other logic is needed, as I assumed that your data was inline with what you claimed it to be 'every 1 minute' and also by its name 1 Min but seemed otherwhise at end day.

gowflow
Data-Convert-V01.xlsm
0
 
Naresh PatelTraderAuthor Commented:
It seems good only two thing to modify.

1. as I run code very 1 row time in sheet 15 Min is 1/1/2014 9:15 but actual it must be 1/1/2014 9:29 ..... row 2 must be 1/1/2014 9:44.

2.Take in to considering only 9:15 AM to 15:30 PM Data per date. i.e. if at the end  1 min data for X date is only 10 rows available & after that  next date started then have to take only 10 rows for calculation for that last bar  & start 15 min calculation from next date data.

Thanks
0
 
gowflowCommented:
ok let me get this right as a bit confusing

RE 1) date ranges are:
row1 from 1/1/2014 9:15  to 1/1/2014 9:29  15 occurence
row2 from 1/1/2014 9:30  to 1/1/2014 9:44   15 occurence
etc..

OR
row1 from 1/1/2014 9:15  to 1/1/2014 9:29   15 occurence
row2 from 1/1/2014 9:29  to  1/1/2014 9:44   16 occurrence

will worry about the cut of date/time later
gowflow
0
 
Naresh PatelTraderAuthor Commented:
RE 1) date ranges are:
row1 from 1/1/2014 9:15  to 1/1/2014 9:29  15 occurrence
row2 from 1/1/2014 9:30  to 1/1/2014 9:44   15 occurrence
etc..

This is Correct one.

Thanks
0
 
gowflowCommented:
I hope this does it. Here is the new code and the new file.

Sub Convert_1to15()
Dim WS As Worksheet
Dim WSConv As Worksheet
Dim MaxRow As Long, MaxRowConv As Long, I As Long, J As Long, Incr As Long, StandardIncr As Long
Dim StRow As Long, EndRow As Long

Set WS = ActiveSheet
MaxRow = WS.UsedRange.Rows.Count

'---> Check if Sheet 15 Min there
On Error Resume Next
Set WSConv = Sheets("15 Min")
If Err <> 0 Then
    Worksheets.Add after:=Worksheets(Worksheets.Count)
    Set WSConv = ActiveSheet
    WSConv.Name = "15 Min"
    WS.Range("1:1").Copy WSConv.Range("A1")
    WS.Range("A:A").Copy
    WSConv.Range("A1").PasteSpecial xlPasteFormats
    MaxRowConv = 2
Else
    MaxRowConv = WSConv.UsedRange.Rows.Count + 1
End If
On Error GoTo 0

StandardIncr = 14
Incr = StandardIncr
'---> Start Process
For I = 2 + Incr To MaxRow Step Incr + 1
    If I <> 2 + StandardIncr Then
        '---> Check to see if Current set of dates falls all within the same date
        If DateValue(WS.Cells(I - Incr, "A")) <> DateValue(WS.Cells(I, "A")) Then
            J = I - Incr
            Do
                J = J + 1
            Loop Until DateValue(WS.Cells(I - Incr, "A")) <> DateValue(WS.Cells(J, "A"))
            Incr = J - 1 - (I - Incr)
            I = I - StandardIncr
        Else
            Incr = StandardIncr
        End If
    End If
    
    WSConv.Cells(MaxRowConv, "A") = WS.Cells(I, "A")
    WSConv.Cells(MaxRowConv, "B") = WS.Cells(I - Incr, "B")
    WSConv.Cells(MaxRowConv, "C") = WS.Application.WorksheetFunction.Max(WS.Range("C" & I - Incr & ":C" & I))
    WSConv.Cells(MaxRowConv, "D") = WS.Application.WorksheetFunction.Min(WS.Range("D" & I - Incr & ":D" & I))
    WSConv.Cells(MaxRowConv, "E") = WS.Cells(I, "E")
    WSConv.Cells(MaxRowConv, "F") = WS.Application.WorksheetFunction.Sum(WS.Range("F" & I - Incr & ":F" & I))
    MaxRowConv = MaxRowConv + 1
    Incr = StandardIncr
    
Next I
End Sub

Open in new window


gowflow
Data-Convert-V02.xlsm
0
 
Naresh PatelTraderAuthor Commented:
Yes perfect only not to take in to account data which is not between 9:15 AM to  15:30 PM time range. & in sheet 1 min after code end, there is selection of range need to be unselected.

Thanks
0
 
Naresh PatelTraderAuthor Commented:
Ok got it....i will post new question regarding this.

Thanks
0
 
Naresh PatelTraderAuthor Commented:
More that excellent. Actually I feel my self happy. Reaching towards my goal. Out of 3 back test 1 is giving sound results. Now testing rest of 2.


Thanks
0
 
gowflowCommented:
OK GREAT !!!
Pls post a link of the ne question in here.
gowflow
0
 
Naresh PatelTraderAuthor Commented:
May I post new question (continuation of this question)?

Thanks
0
 
gowflowCommented:
yes sure
gowflow
0
 
Naresh PatelTraderAuthor Commented:
here it is Data Convertion


Thanks
0
 
gowflowCommented:
I have put a comment in the new question.
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.