time series

  1. A

    Select data concurrant for chart

    Hello, I am trying to plot a bunch of time series via a macro I've written. When the column of data are NOT concurrent (like Range("A:A") and Range("C:C")), it works fine. However if I want to plot Range B s Range A (timestamps in A, data in B), then the graph comes out all messed up (all...
  2. C

    Finding Percentage Drop

    I need a formula that will show the "Header" that correlates with the first time a value drops by at least 90%. In the example below, in the Yellow column, it should say "Q4 2015". Please help! This data package is huge. I need a formula to apply or I may spend the next 10 years doing it...
  3. S

    Convert JSON data to time series data in excel

    For the last 10 years or so I have wanted to convert JSON data to time series data in Excel. I never found an easy solution. This has irritated me. Coinmarketcap has an API for crypto currencies and JSON data https://api.coinmarketcap.com/v2/ticker/ https://api.coinmarketcap.com/v2/ticker/ {...
  4. T

    How to compare different size datasets with varying time increments

    Hi Chandooers, My worksheet does this; I have a workbook which simulates the temperatures and displacements across hundreds of test points within a 2D model of an aircraft engine, at discrete time increments from the start of a flight cycle to the end. It's used for analysis and engineering...
  5. C

    Running percent change formula for time series

    Hello! This is my first post in this forum. I am very happy to be part of it and learn a lot of Excel from all your knowledge and experience :) Is there a way to automatically change the cell references in order to calculate the running percent change, when the year changes? (see row 3) For...
  6. R

    Time series data

    Good day all. I’ve got a time series database for my economic data, and it generally works pretty well except I have one issue around data frequency in my queries. In short, different indicators have different release frequencies, such as daily, monthly, quarterly, annual etc. I have two...
  7. Z

    forecast monthly change of inflation rate

    Hi :) I am working on a project to forecast monthly change of inflation rate. I used SLR but the forecast doesnt seem to be correct. Could someone take a look please? :confused: I am also not sure how to diagnose time series (stationarity, trend, ARMA..) in Excel( is this even possible?) I...
  8. S

    Median Date for time series data

    I have 30 years of time series data for which I would like to obtain approximate median dates for. The data looks like this: <tbody> Date Live 12/27/1976 1 1/9/1977 1 2/22/1977 1 2/23/1977 1 2/24/1977 1 2/27/1977 1 3/1/1977 2 3/2/1977 2 3/3/1977 8 3/6/1977 1 3/7/1977 3...
  9. A

    Time series data scrubbing & summary including dates - VBA solution??

    Hello, I am creating a template that starts with a time series of daily values for the past year (the specific dates will depend on what day I am updating it for). I need to be able to manipulate monthly data from the time series. I am running into issues because there are gaps in the data...
  10. A

    Formula to calculate monthly values from weekly time series, using days in each week

    I want to calculate monthly values from weekly ones, by taking into account the number of days in the weeks that overlap each month. Below is an example. Row 5 shows the figures I want to calculate, by referencing the rows above. <tbody> 03/05/2015 10/05/2015 17/05/2015 24/05/2015 31/05/2015...
  11. U

    Spike Count from Time Series Data Sets

    Hi - in need of help again. I require to count the number of large spikes per week from over two years of, mostly hourly, timestamped data from around 50 data sets. Some sets are in half-hourly intervals. Unfortunately there are cases of missing data, ranging from 1 record up to 140 records...
  12. T

    Cointegration and granger's causality test in Excel

    I have following data: A -> 100 150 123 145 167 200 250 300 270 B-> 290 300 280 276 234 234 288 345 399 How can I perform stationarity,cointegration,Granger's test in Excel?
  13. M

    Array slicing, calculations on dynamic array, rolling fixed period lookback

    I am attempting to pass historical price data for 5 stocks into a 3D array and then do calculations there (and keep in dimension 3). My array is called "matrix" in the workbook attached, and the 3 dimensions are: 1-number of periods (in this case, days), 2-number of stocks and 3-price data and...
  14. M

    VBA - price time series calcs on array - run time error 13

    <tbody> 0 down vote favorite I suspect my question has a very simple answer, and yet I cannot find an answer that I understand in searching all over the site. Ticker1 = an array of stock prices (1 To 2542) PctChg1 =...
  15. R

    Timestamp Continuity Macro Problems

    Hello all, I am looking for some help either A) troubleshooting an existing macro or B) finding another (better?) method for solving the same problem. Because my write-up has proven to be rather lengthy to explain, here is the "TLDR" version: I have a macro that is supposed to fill in gaps in...
  16. W

    Time Series Analysis VBA Transformation

    Hello! I have data from two water pumps - the data is given in start/stop times for the pump activation. So in column A I have dd/mm/yyyy hh:mm:ss and in column B; then "Start"/"Stop". So something like this for each pump... <tbody> <tbody> 30-Jul-11 01:22:42 RUNNING 30-Jul-11 01:28:20...
  17. S

    question working with time series data

    I am working with some Time Series data that has one unique ID per row and columns of gift information: The columns accross the sheet look like this: ID Gift1 date1 Gift2 Date2 Gift3 Date3 Gift4 Date4 Gift5 Date5... etc I am looking to take the maximum gift and its corresponding date and...
  18. S

    Excel Scatter Plot Problem: Date/Time on x-axis

    I have dates (column A), times (Column B), and a dependent variable (Column C) in a straightforward arrangement in Excel 2007. I select both column A and B for the x-axis and in the Data Select window and Excel appears to understand that these columns are the collective x-axis. But on the...
  19. P

    Graphing multiple series over time

    Hey, Any idea how I can show the following in one chart (or any other better way?): August 3rd: sizes____apples________ oranges 1lb ________5 ___________8 2lb ________6___________ 9 3lb ________4___________ 6 4lb ________6___________ 9 etc. August 4th: sizes 1lb ________4...
  20. brettr87

    Floor and Ceiling problem

    Looking for help adjusting my formula in column D (Ceiling) so that instances like D5 would read as 15:15:00 as opposed to 15:00:00. Help is greatly appreciated. Excel...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top