+ Reply to Thread
Results 1 to 4 of 4

Improving Workflow for Handling Large Amounts of Time-Series Data

  1. #1
    Registered User
    Join Date
    03-02-2021
    Location
    Maryland, US
    MS-Off Ver
    2016
    Posts
    2

    Improving Workflow for Handling Large Amounts of Time-Series Data

    I’d like some advice on improving my data analysis workflow. I work in a chemistry lab and am beginning a program which will generate a large amount of time-course data. I’ve provided a simpler example to try and explain the current workflow. Using Excel 2016 but could get my hands on O365 if needed.

    Say we run a series of experiments determining the speed a car accelerates around a given track. We have the time and velocity as the measured variables, and can calculate the acceleration and distance traveled based on that data. The data is collected in a program, processed in a workbook and several parameters (max acceleration, max velocity, total distanced traveled) are also calculated.
    Say I have two experimental data sets for a Honda Odyssey and a Ferrari and I want to compare the velocity, acceleration and distance between the two data sets (see example data sets in the first two screenshots).


    Ferrari.png

    The fastest method I have devised so far is to place both charts in excel, copy the series from Honda plot to the Ferrari plot, creating a new plot with both series. I can compare the Max Speed, Acceleration, etc by then pasting in the appropriate data into a table and making a bar chart.

    Data Comparison.png

    This method is fine for a handful of experiments, but for dozens of conditions with multiple trials for each (40-100 experiments, 8 to 10 plots each, 5,000+ data points/plot), creating these comparison plots becomes unwieldy. Previously I have created giant documents with every data set in it, and just powered through it with formulas. It ain’t fun.

    Ultimately, I am looking for a solution in which:
    1. I can use something like a splicer to select several experiments to compare, and a x-y scatter plot is updated (say velocity vs time) comparing the selected experiment sets.
    2. I can use power query to keep a database of completed experiments by adding files to a folder (which then updates the database)

    This would allow me to quickly compare data sets without having to hunt down/copy paste the original data. I thought that Pivot Charts would be my answer, but they don’t allow scatter plots (wtf) so they are effectively useless for this application. I don’t need a fleshed out solution, any advice pointing me in the right direction would be appreciated. I know I am probably pushing excel to the limits with this, but Excel is so much easier to integrate into reports and presentations than R/MatLab/Mathematica.

    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Improving Workflow for Handling Large Amounts of Time-Series Data

    There are three steps to this process:
    - Import the data
    - Set up named dynamic ranges - once this is done, you are complete forever
    - Set up dynamic charts based on these rages - once this is done, you are complete forever

    The attached word document shows how to set up named dynamic ranges and how to use them in charts.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    03-02-2021
    Location
    Maryland, US
    MS-Off Ver
    2016
    Posts
    2

    Re: Improving Workflow for Handling Large Amounts of Time-Series Data

    Thank You! I never thought of using offset for dynamically capturing an entire column.

    I am working on some ways to make this scalable (essentially all the data for hundreds of experiments is held in a database that automatically updates with Power Query) and then I can select which data is imported and processed using slicers. I then use named dynamic ranges to generate the plots.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Improving Workflow for Handling Large Amounts of Time-Series Data

    I rarely make charts off pivot tables. Charts based off pivot tables have a lot of limitations. So, I frequently overlay the results of the pivot table with a named dynamic range.

    Power Query is a "quick and dirty" way to import data and it is quite tolerant of whether you have rows above where your column headers start.

    There is another way to import data called MS-Query. It is moderately more difficult to set up, but is a lot more powerful. One of the things you can do with it is pass parameters to it from cells on the spreadsheet. So you don't have to import all your data. This works only if the query can be represented graphically. Most queries can.

    I have, in a former job, used very complex SQL, joining tables in Oracle databases, etc. These cannot be represented graphically. However, I have a small bit of code that takes a SQL "template" and substitutes real values to make executable SQL. Mostly I used it to pass dates, but in one instance, I used it to change the table the SQL code read. I've also used it to modify IN statements to include a list of models that were hot for that week. It is harder to set up, but it is as powerful as you can write SQL. It plays very nicely with Oracle and so far, so good when Excel is used as the database.

    If you have a lot of complex data to download, it's probably a good option. If you are pulling a simple download of a thousand or so records, then Power Query is probably better.

+ 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. Improving PivotTable Slicer Performance With Large Data Sets
    By ncalenti in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2019, 05:27 AM
  2. Handling large data sets
    By chengafni in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-03-2017, 04:22 AM
  3. [SOLVED] HELP - This Macro works on small amounts of data but fails on large amounts
    By BookmanNLA in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-18-2015, 12:40 AM
  4. Entering large amounts of series into a graph
    By neongig in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-08-2013, 10:40 PM
  5. Large data, time series data analysis
    By calhawk01 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2013, 03:42 PM
  6. [SOLVED] Improving my Error Handling With Arrays
    By freakytiki34 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-23-2012, 05:14 PM
  7. Replies: 1
    Last Post: 04-23-2006, 05:50 PM

Tags for this Thread

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