Solved

# Data convertion

Posted on 2014-02-19
Medium Priority
186 Views
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
0
Question by:Naresh Patel
• 13
• 13

LVL 20

Expert Comment

ID: 39870105
Is there a question here?
It looks like an assignment request.

Scott C
0

LVL 31

Expert Comment

ID: 39870149
I would say looks more like a project
gowflow
0

LVL 8

Author Comment

ID: 39870174
Please suggest Experts, what you want me to do.Split question - Delete Question - Reframe Question.

Thanks
0

LVL 31

Expert Comment

ID: 39870288
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

LVL 31

Expert Comment

ID: 39872579
What did you decide ?
gowflow
0

LVL 8

Author Comment

ID: 39872669
What did you decide ?
splitting

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

Thanks
0

LVL 31

Expert Comment

ID: 39873166
what do you mean convert 1 min to 15 minutes ? not clear
gowflow
0

LVL 8

Author Comment

ID: 39873218
See attached I had manually created Sheet "15 Min" which is desire result.

Thanks
Data.xlsx
0

LVL 8

Author Comment

ID: 39873219
but not whole calculation has done just 3 row data calculated.

Thanks
0

LVL 31

Expert Comment

ID: 39873248
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

LVL 8

Author Comment

ID: 39873289
Pls give me 15 min
Thanks
0

LVL 8

Author Comment

ID: 39873350
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

LVL 31

Expert Comment

ID: 39873354
ok nevermind got it !!

gowlfow
0

LVL 31

Expert Comment

ID: 39873560
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
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
``````

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

LVL 8

Author Comment

ID: 39875965
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

LVL 31

Expert Comment

ID: 39876162
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

LVL 8

Author Comment

ID: 39876223
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

LVL 31

Expert Comment

ID: 39876424
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
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
``````

gowflow
Data-Convert-V02.xlsm
0

LVL 8

Author Comment

ID: 39876434
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

LVL 31

Accepted Solution

gowflow earned 2000 total points
ID: 39876516
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

LVL 8

Author Comment

ID: 39876574
Ok got it....i will post new question regarding this.

Thanks
0

LVL 8

Author Closing Comment

ID: 39876581
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

LVL 31

Expert Comment

ID: 39876766
OK GREAT !!!
Pls post a link of the ne question in here.
gowflow
0

LVL 8

Author Comment

ID: 39881653
May I post new question (continuation of this question)?

Thanks
0

LVL 31

Expert Comment

ID: 39881767
yes sure
gowflow
0

LVL 8

Author Comment

ID: 39881780
here it is Data Convertion

Thanks
0

LVL 31

Expert Comment

ID: 39882331
I have put a comment in the new question.
gowflow
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final pâ€¦
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calculâ€¦
###### Suggested Courses
Course of the Month14 days, 12 hours left to enroll