Solved

Data convertion

Posted on 2014-02-19
27
173 Views
Last Modified: 2014-02-24
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
Comment
Question by:itjockey
  • 13
  • 13
27 Comments
 
LVL 20

Expert Comment

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

Scott C
0
 
LVL 29

Expert Comment

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

Author Comment

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

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
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 29

Expert Comment

by:gowflow
ID: 39872579
What did you decide ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39872669
What did you decide ?
splitting

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

Thanks
0
 
LVL 29

Expert Comment

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

Author Comment

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

Thanks
Data.xlsx
0
 
LVL 8

Author Comment

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

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
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

by:itjockey
ID: 39873289
Pls give me 15 min
Thanks
0
 
LVL 8

Author Comment

by:itjockey
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 29

Expert Comment

by:gowflow
ID: 39873354
ok nevermind got it !!

just read your question again it is all there
gowlfow
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 29

Expert Comment

by:gowflow
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
    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
 
LVL 8

Author Comment

by:itjockey
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 29

Expert Comment

by:gowflow
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

by:itjockey
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 29

Expert Comment

by:gowflow
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
    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
 
LVL 8

Author Comment

by:itjockey
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 29

Accepted Solution

by:
gowflow earned 500 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

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

Thanks
0
 
LVL 8

Author Closing Comment

by:itjockey
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 29

Expert Comment

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

Author Comment

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

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39881767
yes sure
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39881780
here it is Data Convertion


Thanks
0
 
LVL 29

Expert Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now