+ Reply to Thread
Results 1 to 2 of 2

Macro buttons to create historical charts by month of minute-interval data

  1. #1
    Registered User
    Join Date
    04-17-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2016
    Posts
    33

    Macro buttons to create historical charts by month of minute-interval data

    Hi.
    I think (or perhaps hope) this type of question may have been raised before, so a link to a solution would be great.
    I have around ten Workbooks of historical data but only containing between five and 14 months of data each. However, the time interval is only one minute between records, so 30 days of data involves 43,200 data points (per series) and 365 days contains 525,600 data points.
    I would like to create a macro button for each month, so if there are six months of data e.g. from mid-December 2016 to some arbitrary date in May 2017, there would be six working macro buttons, one for each month. The button would produce an X-Y Scatter chart, plotting the B, C and D columns of data against the time-stamp data, in column A, for the specified Month.
    See the attached file showing an example of a chart produced when pressing the December 2016 macro button. The existing chart could be deleted once a new macro button was pressed but this is not essential (I could just delete it when finished).
    Since there are so many data points per chart I would like the Marker size = 2 and the Line size = 0.75, but I could do this by hand.
    The labeling of chart Title and Axes, and display of the Legend would be great, but not essential.

    Is this too much to ask? Apologies if so.

    Note: I had to drastically delete rows from worksheet to be able to upload. Original sheet had data from May 2016 to May 2017 (around 520,000 rows).
    Attached Files Attached Files
    Last edited by GeoffH1; 05-21-2017 at 04:05 PM. Reason: Explain small file size

  2. #2
    Registered User
    Join Date
    04-17-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2016
    Posts
    33

    Re: Macro buttons to create historical charts by month of minute-interval data

    Hi again. I have found another method:
    I have made a Template Workbook, with tabs for each Month (Dec, Jan, etc.). I import a large data file into the Workbook, containing, say, six months of 1-minute interval data. I also have VBA code to copy each month of data onto each respective monthly tab, containing the time-stamps (column A) and the variables of interest (columns B, C and D). For each tab, I select all the data (Ctrl A) and Insert Template chart (which has Line width, Marker size, Axis title font size at desired level). The VBA code only copies one month at a time, and takes about 30 seconds to copy each month of data to a new tab: I will be getting a new computer (32 GB RAM, Quad core i7) which will hopefully speed things up a bit. I can't access the code right now, but will post later.
    My intention is to record a Macro button on the Workbook Template, to quickly generate each chart.

    The code to copy Dec-2016 and Jan-2017 is:
    Sub data_transfer()
    Dim dt1_strt As Date
    Dim dt1_end As Date
    Dim dt2_strt As Date
    Dim dt2_end As Date
    Dim dt_chk As Date
    Dim n As Long
    Dim j As Long

    dt1_strt = "01 / 12 / 2016"
    dt1_end = "01 / 1 / 2017"
    dt2_strt = "01 / 1 / 2017"
    dt2_end = "01 / 2 / 2017"

    n = 1
    j = 1
    k = 1
    While Sheets("Sheet1").Cells(n, 1).Value <> ""
    dt_chk = Sheets("Sheet1").Cells(n, 1).Value
    If (dt_chk >= dt1_strt And dt_chk < dt1_end) Then
    Sheets("Dec16").Cells(j, 1).Value = dt_chk
    Sheets("Dec16").Cells(j, 2).Value = Sheets("Sheet1").Cells(n, 2).Value
    Sheets("Dec16").Cells(j, 3).Value = Sheets("Sheet1").Cells(n, 3).Value
    j = j + 1
    ElseIf (dt_chk >= dt2_strt And dt_chk < dt2_end) Then
    Sheets("Jan17").Cells(k, 1).Value = dt_chk
    Sheets("Jan17").Cells(k, 2).Value = Sheets("Sheet1").Cells(n, 2).Value
    Sheets("Jan17").Cells(k, 3).Value = Sheets("Sheet1").Cells(n, 3).Value
    k = k + 1
    End If
    n = n + 1
    Wend

    End Sub
    Last edited by GeoffH1; 05-23-2017 at 03:40 AM. Reason: To include VBA code

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA - Create 15 minute interval bases on start and enddate input
    By Boskant in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-25-2016, 10:07 AM
  2. Converting 1 minute data to a chosen time interval
    By James Bowler in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-09-2016, 06:06 AM
  3. excel vba switch to all sheets after interval of 1 minute
    By cpramesh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-26-2014, 01:59 AM
  4. Looking to convert 10 minute interval data to 15 minute interval data
    By rsporseen in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-03-2013, 01:01 PM
  5. Daily Tracking sheet with 15 minute interval
    By raghavbhagat in forum Excel General
    Replies: 0
    Last Post: 09-30-2013, 07:25 AM
  6. Making a time (15 Minute) interval exclusive
    By gottabarra in forum Excel General
    Replies: 4
    Last Post: 05-07-2013, 11:07 AM
  7. Calculate how many records per 5 minute interval
    By riccaliolio in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2011, 06:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1