Creating Day Values from Summarized Information

[chirayu]

Hi All,

What I mean by this is I have data like the below.

Essentially I want to create a line chart that shows day by day data instead of jumping from point to point.

I know I can do this by creating a data sheet & using Lookup to generate day by day data but that would increase filesize by a lot.

So I was wondering whether there is another way to do it

Sample Data:

Carrier    FROM        TO          PRICE
A          01-Jan-17   08-Jan-17   2200
A          09-Jan-17   14-Jan-17   2500
A          15-Jan-17   05-Feb-17   2500

Chart should create daily data using above summary

 

[Chihiro]

I mostly do this type of time intelligence inside Data Model using DAX generated dynamic calendar table. It has minimal impact on performance and size of the workbook.

But if I recall you have Excel 2007 right? So DAX isn't an option here.

Do you need the missing/gap dates showing 0 value? If not, if you choose date type axis, charts will automatically fill gaps.



If you need missing dates to show 0, then you'd need lookup table.

 

[p45cal]

Note: This answer and attached file have been updated since first posting (to rectify appearance of dates).

Click the button in the attached, which runs this snippet:

Sub blah()
Dim xvals(), yvals()
ReDim xvals(1 To 1): ReDim yvals(1 To 1)
idx = 0
Set CellsToProcess = Range("A1").CurrentRegion.Columns(2)
Set CellsToProcess = Intersect(CellsToProcess, CellsToProcess.Offset(1))
For Each cll In CellsToProcess.Cells
  For dy = cll.Value To cll.Offset(, 1).Value
  idx = idx + 1
  ReDim Preserve xvals(1 To idx): ReDim Preserve yvals(1 To idx)
  xvals(idx) = CLng(dy)
  yvals(idx) = cll.Offset(, 2).Value
  Next dy
Next cll
With ActiveSheet.ChartObjects.Add(100, 100, 300, 300).Chart
  .ChartType = xlXYScatterLines
  With .SeriesCollection.NewSeries
  .XValues = xvals
  .Values = yvals
  End With
  With .Axes(xlCategory).TickLabels
  .NumberFormat = "dd/mm/yyyy"
  .Orientation = 90
  End With
  Application.ScreenUpdating = True
  .PlotArea.Height = 225
End With
End Sub

 

[chirayu]

Thanks @Chihiro and @p45cal

I've attached a sample file based on actual data that I ended up creating as a quick solution

 

[Chihiro]

That's probably one of the better way to go about it. No matter what you do, missing data points will cause chart to either drop or just draw straight line to next data point. So creating table with all possible data point is the best way to go.

 

[r2c2]

@chirayu

Interesting question. You do not need to expand data for all dates. Excel line charts are smart enough to do that for you. That said, given that you have some missing dates in your data, you just need to 2x the data and rearrange it like below. For missing dates, I have used TODAY(). You can replace this with a future date if needed.




See attached workbook with formulas and chart for this.

All the best.

 

[chirayu]

@r2c2 nifty trick but seems the issue still remains. Take CompC for example.

Comparing the data for CompC there is no rate from 1-Feb to 28-Feb. Requirement is that the chart has to carry the previous rate through till 1-Mar data point. So for the period of 1-Feb to 28-Feb the value would be the same as that for the period of 1-Jan to 31-Jan i.e. 2550.

Comparing your chart to mine, you'll notice the line declining from 31-Jan down to 1-Mar. Instead of a straight line till 28-Feb & then dip down to 1-Mar.

That's why I had to "fill in the blanks" so to speak.

 

[p45cal]

The attached has a button on sheet DATA to click to produce a new chart. It is a fairly hefty tweak of my previous offering to:
a) cope with the new layout of data
b) cope with multiple companies
c) plot only points where changes occur, to save data/resources (each plot only has just enough points to show what's necessary) [Note, this isn't quite true; where you have similar prices on different dates next to each other, as you have in rows 2 and 3 of your DATA sheet, both are still plotted, but don't really need to be]

Downsides:
a)the data has to be sorted (it already was) before running the macro
b) it's an x-y scatter plot, so chart x-axis tick-marks aren't handled well (not the first of each month)
c) no multilevel x-axis labels
d) if the user does't understand vba then it's difficult for the user to tweak (although it will handle larger data sets, with more or fewer companies, without adjustment)

Upsides:
a)fast and automatic
b)handles missing data as you want it to
c)very few resources used when done (no extra sheet, no extra table/data/formulae on any sheet)


Note that column C is not used at all to generate points, although I do use it to check for a maximum date to scale the whole chart.

 

[chirayu]

@p45cal that's a pretty cool solution. will definitely read the macro more & figure out exactly how it works & probably come back on this thread if I don't understand certain parts lol.

CompB rates are only from 21-Mar to 31-Mar. However the chart carries the data point through to end of date range. Logically however this data point would not carry forward as there are no further rates for this company. Rates only carry forward to next point if future rates are available. otherwise its essentially like a false positive.

 

[p45cal]

Tweaked accordingly.