Line Chart (Stock Intraday Chart)
I would like to create a Line Chart with the values from 2 cells.
1. Stock Price
2. Time
These two cells whose values get updated every minute using a Web Query.
How can i accomplish to plot and update the chart continously from the values of just these two cells.
Its easy if i have a range of cells and its straightforward.
Do i have to create a new row for every time the value in these cells updated and these set of rows act as a Data Table.
Please help me.
Thanks in advance.
Conventionally, charts like to be supplied with a range of values from the Worksheet, so the typical answer would be to add a new row everytime the stock price updated.
However, try the following code....
Option Base 1
Private Sub Worksheet_Change(ByVal Target As Range)
Static TimeValues(), StockPriceValues(), DontRecord As Boolean
If Intersect(Target, Range("A5:B5")) Is Nothing Or DontRecord Then Exit Sub
If Range("A5") = "End" Then DontRecord = True
On Error GoTo StartRecording
ReDim Preserve TimeValues(UBound(TimeValues) + 1)
ReDim Preserve StockPriceValues(UBound(StockPriceValues) + 1)
GoTo SetValues
StartRecording:
If MsgBox("Do you want to start recording new data for this chart?", vbYesNo) = vbNo Then
DontRecord = True
Exit Sub
End If
ReDim TimeValues(1)
ReDim StockPriceValues(1)
SetValues:
On Error GoTo 0
TimeValues(UBound(TimeValues)) = Range("B5")
StockPriceValues(UBound(StockPriceValues)) = Range("A5")
ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).XValues = TimeValues
ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).Values = StockPriceValues
End Sub
You will have to change reference to "A5" to whatever the cell is for the latest stock price and "B5" to whatever the time value is.
The code assumes you've already set up a chart (change reference to "Chart 1" if it's a different name).
The code maintains an array of values and times on every occasion the "A5" or "B5" values are changed, and puts all the values to-date into the chart.
When the worksheet is first opened the user is asked if they want to start recording new data (the use can answer 'no' and the previous chart will be preserved).
Also, by manually entering 'End' into "A5" the recording will stop.
If required, the chart can be copied and pasted to a separate worksheet as a historical record (as the data stays with the chart and is independent of the code).
Last edited by Loz; 02-14-2007 at 10:57 AM.
Hi,
For an excellent source of inspiration ...
http://www.peltiertech.com/Excel/Cha...artTricks.html
Hi Loz
Thanks for your effort and the code. It works nicely for about 12 quotes. Then i am getting the error 'Unable to set the XValues property of the series class'.
After 12 refreshes it fails with the runtime error.
Please reply.
Thanks.
Last edited by prabs; 02-21-2007 at 07:09 PM.
I've been away for a few days, and just caught up with the forum activity.
I've replicated the problem you encountered. It seems Excel limits what can go in the XValues box to 256 characters (similarly for the 'Y' values box also). As time values are represented by numbers to several decimal places the XValues box quickly runs out of space.
As you are updating the chart every minute and graphing stock movements presumably throughout the entire trading day, a different approach is needed.
You were probably right in assuming that the answer is to simply copy the new values down the rows and let the chart pick up the new range of values.
I've therefore done some new code for you, which is triggered whenever a cell on the sheet changes....
Private Sub Worksheet_Change(ByVal Target As Range)
Static DontRecord As Boolean, Recording As Boolean ' values start as FALSE when initialised
Dim Rng As Range
If Intersect(Target, Range("A5:B5")) Is Nothing Or DontRecord Then Exit Sub
If Range("A5") = "End" Then
DontRecord = True: Recording = False
Exit Sub
End If
If Not Recording Then
If MsgBox("Do you want to start recording new data for this chart?", vbYesNo) = vbNo Then
DontRecord = True: Recording = False
Exit Sub
End If
Recording = True: DontRecord = False: Range("A6:B65536").ClearContents
End If
Set Rng = Range("A65536").End(xlUp).Offset(1, 0)
Rng.Value = Range("A5").Value
Rng.Offset(0, 1).Value = Range("B5").Value
ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).XValues = Range("B6", Rng.Offset(0, 1))
ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).Values = Range("A6", Rng)
End Sub
You will have to modify it if you prefer the column of values to be in a different place.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks