Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating destination when I add a line to my source workbook | Excel Worksheet Functions | |||
Getting line on line chart to display after creation from Pivot Table | Charts and Charting in Excel | |||
How to add a Vertical Line to a Column or Line Chart with two axes already in use? | Charts and Charting in Excel | |||
Make a line in a bar chart, and change color of any bars that exceed the line | Excel Discussion (Misc queries) | |||
Use a multiplier to change List Prices to Net prices | Excel Worksheet Functions |