Chart Data Recovery

[CLarke]

I have a dilemma I have a spreadsheet that only has a chart in it, that was
linked to another spreadsheet. The other spreadsheet has disappeared (HD
died and data was not recoverable and no backups were made). I can see that
the chart still has all of the correct data points in it, because I cna hover
the mouse over the line and it will tell me the values.
Is there a way to extract the data from the chart so I can rebuild my
original spreadsheet. The amount of data is 4 daily readings of 4 different
values from the Financial markets over thelast 4 years, so it is a lot of
data.

Any suggestions would be greatly appreciated.

Clarke

 

[Ed Ferrero]

Click on one of the series in the chart, you will see the series formula in
the formula bar at the top of the screen. It will look something like this;
=SERIES("Series name",Sheet1!$A$1:$A$3,Sheet1!$B$1:$B$3,1)

Then click on the formula in the formula bar, and press F9. The series
formula will now show values. Like this;
=SERIES("Series name",{1,2,3},{4,5,6},1)

Select the whole formula (or just the bits between curly brackets) and copy
it to a worksheet cell. Use Data -> Text to Columns to parse the values into
something you can use.

Ed Ferrero
www.edferrero.com

 

[CLarke]

Thanks Ed this worked like a treat.

Clarke

 

[CLarke]

Thanks Ed, I did some analysis last night and it seems to only extract a
certain amount. It seems to have a predefined limit int he number of
characters it will display in the formula bar. It converts all of the dates
to number format, ie 36789, which is fine, and when you format the cell in
excel where the new data is going, it gives you the data correctly. However
it does not bring in all of the data points. It only converts the fist
300-400, I have over 1000 data points in each line on the graph.

Any ideas.

 

[Jon Peltier]

I suspected that would be an issue. Here's a quick and dirty sub for
extracting a chart's data:

Sub ExtractChartData()
Dim iSrs As Long
Dim cht As Chart
Dim srs As Series
Dim ws As Worksheet

Set cht = ActiveChart
Set ws = Worksheets.Add
For iSrs = 1 To cht.SeriesCollection.Count
Set srs = cht.SeriesCollection(iSrs)
ws.Cells(1, 2 * iSrs).Value = srs.Name
ws.Cells(2, 2 * iSrs - 1).Resize(srs.Points.Count).Value = _
WorksheetFunction.Transpose(srs.XValues)
ws.Cells(2, 2 * iSrs).Resize(srs.Points.Count).Value = _
WorksheetFunction.Transpose(srs.Values)
Next
End Sub

- Jon

 

[John Mansfield]

In addition to Jon's macro, Microsoft provides a Knowledge Base article with
another snippet of code to extract data from a chart.

http://support.microsoft.com/kb/300643

--
John Mansfield
http://cellmatrix.net

 

[Jon Peltier]

The Microsoft macro assumes all series in the chart share the same X values.
Mine assumes that each series has distinct X values. If theirs is wrong you
lose data. If mine is wrong you get duplicated data.

- Jon

 

[CLarke]

Thanks a lot guys you made a 75 year old man very very grateful.