updating prices for a line chart
 

I apologise first as similar questions have been asked before, I
guess being honest I just did not understand the answers !
I want to chart share prices with a line chart. I have 20 company
names in A1:T1. Their current prices are imported via another
programme and show in B2:T2.
I have no problem in making a chart showing the company names and
their current prices, but the problem is when the share price
changes. All that happens at the moment is the price is overwritten
and all I see on the chart is the current price (previous price is
lost).
How can I get the line chart to show old prices and the current
price ?
Thanks

updating prices for a line chart
 

Don't let the other program obliterate the previous prices. Charts only
chart data that is in the source data range, not what may once have been
there. Either move the previous data prior to the update, or have the update
place the new numbers in the next row.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
ups.com...
I apologise first as similar questions have been asked before, I
guess being honest I just did not understand the answers !
I want to chart share prices with a line chart. I have 20 company
names in A1:T1. Their current prices are imported via another
programme and show in B2:T2.
I have no problem in making a chart showing the company names and
their current prices, but the problem is when the share price
changes. All that happens at the moment is the price is overwritten
and all I see on the chart is the current price (previous price is
lost).
How can I get the line chart to show old prices and the current
price ?
Thanks

updating prices for a line chart
 

Don't let the other program obliterate the previous prices. Charts only
chart data that is in the source data range, not what may once have been
there. Either move the previous data prior to the update, or have the update
place the new numbers in the next row.

- Jon


Thanks for that. That is the problem i have.
The prices can update every second, so copy and paste is not an
option.
There must be some code that lets the data feed the chart and then
move the data or let the chart be updated from different rows ?

updating prices for a line chart
 

I've worked with clients to provide this functionality, essentially a
routine that detects an update (using worksheet_calculate) and copies the
new data to a table of previous results on another sheet; this table is used
as the chart's source data range.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
oups.com...

Don't let the other program obliterate the previous prices. Charts only
chart data that is in the source data range, not what may once have been
there. Either move the previous data prior to the update, or have the
update
place the new numbers in the next row.

- Jon


Thanks for that. That is the problem i have.
The prices can update every second, so copy and paste is not an
option.
There must be some code that lets the data feed the chart and then
move the data or let the chart be updated from different rows ?

updating prices for a line chart
 

On 10 Jun, 01:16, "Jon Peltier"
wrote:
I've worked with clients to provide this functionality, essentially a
routine that detects an update (using worksheet_calculate) and copies the
new data to a table of previous results on another sheet; this table is used
as the chart's source data range.

- Jon


Thanks Jon. I have decided to ditch the graph and just use figures. I
will be able to watch trends by highlighting high, low and average of
each price.

updating prices for a line chart
 

Does this mean you know how to archive the data already, just not chart it?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
ups.com...
On 10 Jun, 01:16, "Jon Peltier"
wrote:
I've worked with clients to provide this functionality, essentially a
routine that detects an update (using worksheet_calculate) and copies the
new data to a table of previous results on another sheet; this table is
used
as the chart's source data range.

- Jon


Thanks Jon. I have decided to ditch the graph and just use figures. I
will be able to watch trends by highlighting high, low and average of
each price.

updating prices for a line chart
 

On 11 Jun, 05:00, "Jon Peltier"
wrote:
Does this mean you know how to archive the data already, just not chart it?

- Jon
-------

Jon

A guy at work kindly gave me some code - i now have a command button
and every click i get the current prices in the next free column. I
had the problem that formulas were being copied when i needed values,
but the code i have seems to have got around that. Although i am still
putting together the program to meet my needs. Eventually i shall get
the code to click in everytime theres a price change.

Dim looper, across_number As Integer

across_number = Worksheets("data").Cells(2, "B")
If across_number = 0 Then across = "A"
If across_number = 1 Then across = "B"
(above line is repeated by an increase of 1 of the number and letter)
For looper = 1 To Worksheets("data").Cells(1, "B")

Worksheets("show").Cells(looper, across) =
Worksheets("data").Cells(looper, "A")

Next


i have to enter the if across_number line for each entry, at the
moment i`m up to - If across_number = 51 Then across = "AZ"

do you know any smarter code so i do not have to enter that line for
each column ?

thanks

updating prices for a line chart
 

You can refer to column number instead of letter:

Worksheets("data").Cells(2, 2)

If you want the value in a cell, use

Worksheets("data").Cells(2, 2).Value

I think you can make it much easier with this:

Worksheets("show").Cells(looper, across_number + 1).Value = _
Worksheets("data").Cells(looper, "A").Value

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"charlie" wrote in message
oups.com...
On 11 Jun, 05:00, "Jon Peltier"
wrote:
Does this mean you know how to archive the data already, just not chart
it?

- Jon
-------

Jon

A guy at work kindly gave me some code - i now have a command button
and every click i get the current prices in the next free column. I
had the problem that formulas were being copied when i needed values,
but the code i have seems to have got around that. Although i am still
putting together the program to meet my needs. Eventually i shall get
the code to click in everytime theres a price change.

Dim looper, across_number As Integer

across_number = Worksheets("data").Cells(2, "B")
If across_number = 0 Then across = "A"
If across_number = 1 Then across = "B"
(above line is repeated by an increase of 1 of the number and letter)
For looper = 1 To Worksheets("data").Cells(1, "B")

Worksheets("show").Cells(looper, across) =
Worksheets("data").Cells(looper, "A")

Next


i have to enter the if across_number line for each entry, at the
moment i`m up to - If across_number = 51 Then across = "AZ"

do you know any smarter code so i do not have to enter that line for
each column ?

thanks