Excel 2003 :: Daily Stock Chart AND Random Scatter Chart Data
Jan 14, 2014
I need to plot various data on top of stock charts.
This data could be irregular in date: it could be weekly, or simply random.
To plot this data by itself requires a Scatter Chart.
From what I know so far, you CANNOT DO THIS.
However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.
Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)
But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.
I am using the the stock chart and I would like there to be a horizontal line across the chart with a y-axis of the opening price. In the end I want this to be in VBA but how to do this manually I should be able to put it in loop of data that will produce many charts with a horizontal line starting at the day opening.
I have tried to add a new series to the chart but I'm using the Candle stick stock chart and the new series comes out as a vertical not horizontal line. I read about using a secondary horizontal axis but all the tutorials I find are in Excel 2003 and they donlt line up well with Excel 2007.
I found this link on charts with horizontal lines but again they are in 2003 and I'm using a candle stick stock chart not a scatter chart. [URL]
I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
I would like combine (overlay) a bubble chart with a connected point scatter chart. I understand that, without VBA, this is not possible. However, I understand that, by using VBA, the markers of a scatter chart can be configured as circles with their size proportional to values in a specified column. This pseudo-bubble chart can then easily be combined with a connected point scatter chart.
My question is: does anyone have any VBA code to share that shows how to configure a scatter chart as the type of pseudo-bubble chart described above.
I work in a call centre and I record my daily figures. My target is 82%, and I would like a bar chart in Excel 2003 to colour the chart according to wether I hit target or not. If my daily figure >=82% the bar should be green.
I would like to combine / overlay a bubble chart and scatter chart with straight connectors.
I understand that, without VBA, it is not possible to combine a bubble chart with a scatter chart.
Unfortunately, I am a VBA newbie, and so I cannot write my own code (though I can usually adapt code to my specific environment).
My question is: does anyone have VBA code to share that will convert a scatter chart series (x,y data in 2 columns) to a bubble-style chart (bubble radius in 3rd column)?
I'm working on a criteria matrix in Excel 2010 that automatically plots a single member in a Scatter Chart based on the two values. There are 4 suppliers listed in Column D starting in cell D4 thru D7. The "x" value is listed in Column E starting in cell E4 thru E7. The "y" value is listed in Column F starting in cell F4 thru F7.
The scatter chart will plot the points correctly, however, there are two issues: 1) If I try to insert a data label using the "Series Name," or in this case, the supplier's name, it will lists ALL of the suppliers Column D. It will not list the single supplier listed in cell D4. 2) The scatter chart appears with gridlines as a 4x4 matrix with a total of 16 cells. The "x" and "y" axis both start at 0 and go to 4. I can shade the entire chart one color. However, I want to shade some of the cells with darker and lighter shades.
My X axis range is small 1 to 4. but I want to show minor grid lines at 0.5, 1.5, 2.5, and 3.5 only. I do not want lines at 1, 2, 3 or 4. I am not sure how to achieve this. as switching on minor grid lines, included those at the integers as well as at the half way points.
I'll attempt to explain what I'm hoping to do. I have a column of data that I want to plot out into a chart. For example the data points might run from -1.50 to 2.5.
Let's say there are 20 data points in the column and they are spaced thruout the range shown above. They tend to cluster amoung themselves into 2 or 3 distict groups.
I want to first produce one horizontal bar that runs from the -1.50 to 2.5 overall range and secondly, I want to overlay a scatter chart horizontally over the bar showing where the data points lie within the range and also so I can visually see the clusters of points.
With the data residing in a column, when I generate the scatter chart, the points are laying verticle within the chart and spreadout horizontally. I can manually get what I want by narrowing the chart down to the point that the points lie on a straight vertical line, but I haven't been able to figure out how to "rotate" the chart 90 deg to overlay the scatter points within the bar chart.
Naturally, the optimum solution would be such that I could generate the charts originally and then allow the spreadsheet to generate the new charts as we modify the data values, etc.
I'm working on a workbook for tracking debt. It has 15 sheets. Instructions, Charts (for showing debt change over time), "Invisible" (this is where my chart data is, I plan to hide it), and 12 month sheets. On the Invisible sheet, for example, I have my data set up. I'm pulling the data from other sheets. In the screenshot, I'm using =Month12!P48 as the formula to pull my data from the other sheets. But, the user hasn't entered data yet for month 12, so the line chart drops to zero there. I want it to just stop and do nothing, until there is data in that cell. (I would prefer to use a line chart vs a scatter chart for this, but I have the same problem in both)
I have a series of X-Y Data (0-10 in both axis) that I want to overlay on an area chart to show outliers outside of an accepted condition. I have been able to create the area chart and the scatter chart separately but when I try to combine them the axis gets messed up. I tried using a secondary axis but I am not that skilled
I have attached the data and my feeble attempts at creating this chart.
Does anyone know if there's a way to color code points on a scatter chart based on their values in a "3rd dimension" (i.e. other than the 2 dimensions represented by the chart axes).
You might imagine a scatter chart that plots transactions by price (y) and units purchased (x).
In that scenario, it might also be nice to color code each point by the region that made the sale.
The only way I know to do this is to manually create separate columns of data for each region, so that each region can be plotted on the scatter chart as a separate data series.
But since I'd like to experiment with several color coding alternatives, I'm wondering if there isn't a simpler way...
For instance, Rob Bovey's "XY Chart Labeler" can add data point labels to a scatter chart...
Is there, perhaps, another add-in that might enable color coding of data points based on a 3rd set of values?
I have to create a chart XY Scatter plot in excel. I have to differentiate the data points color based on the another column "category". Now I can change the data points color manually. Since the number of points is huge, is it possible to color the data points based on a column?
I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2) these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
I have 3 test scores for each student. Currently, I have 3 students.
For example :
John 95 90 85 Cindy 50 60 100 Dan 87 86 90
I have a chart that that plots 3 lines, one for each student.
However, I want the chart to dynamically update if there's another student. In other words, I would like the chart to add another series while the legend updates to include another student.
Data: starting from row1 Column A Column B Column C Supplier Sales Rating Sup1 100 5 Sup2 100 3 sup3 65 5 sup4 80 4
Column B (Sales) is the X Axis and Column C (Rating) IS THE Y Axis
Query: On the Scatter chart when I want to show data Labels or when I hover the Mouse over the Points (100,5), for example, how do I get the corresponding Supplier Name to show as well ?
I have some data that I'm plotting on a bar chart and I'm trying to "HIDE" the columns with zero or null values. Basically, if the column is blank, I don't want a "gap" on the chart. I'm not getting this to work.
I'm using Windows XP with MS Excel 2003. I have a pivot table representing a survey. Let's say I've built the survey outside of excel and I've imported the response data into Excel. One of the questions in the survey is "ratings" and the possible valid responses for it is: "Excellent", "Good", or "Poor". In my data set in excel let's say I have 10 responses or rows and all the responses for the question on ratings are either "Excellent" or "Good". (There are no rows with a "Poor" value in the ratings column).
For example, let's say out of the 10 responses, 6 are "Excellent" and 4 are "Good". As such my Pivot chart shows two bars: one for the number of respones with "Excellent" (10) and another bar for the number of responses with "Good" (4). My delima is how to show a third bar showing "Poor" with a zero as the number of responses.
I have, _________X________Y Brown ____1.5 ______0.1 Black _____1.2______ 0.5 White____ 1.9 _______0.2 Red ______1.1 _______0.9 Yellow____ 2.0 _______0.4 Green _____1.8 _______0.7
Now the Colors are the lables I need to put in each x and y data.
Attached I have my data - there is stock price information (monthly basis) as well as analyst upgrades / downgrades / or initiations.
Is there a very neat way to create an annotated stock chart for these ocurences - perhaps using symbols along a line chart that pots out the monthly stock price on the y axis and the months on the x axis.
The attached file has a scatter chart in which all of the data is properly displayed except the X axis. How can I get my X axis to reflect the data I have selected?
I have 200+ XY pairs I want to chart and see a visual representation of their distribution. Then I want to cursor over exceptional points and see their identity. I don't need (nor want) data labels, disparate markers, nor a legend. I just want the points scattered on the xy plane. Also, the data set changes depending on user-controllable filter criteria.
There are two problems: 1. If I build it through the wizard as a single data series, everything is fine except that the charting process seems to replace the X value of the XY pair with a sequence number once I swap in a new data set, and ... 2. I seem to loose the individual point identities for cursor-over purposes.
The only solution I can think of for these issues is making each XY pair a one-point Series, but that's a hugely laborious way to do it. If that's it though, is there a way to globally load the independent data series and set every data point marker and color to be the same?
However, if I could do it as a single series, then I still have the issues above. Is there a way to prevent Excel from converting the X values to sequence numbers once a new data set is swapped in, and is there a way to identify the individual points for cursor-over?
Naturally, the first column contains someone's name. The other columns contain XY coordinates. These coordinates start at 375 and end at 499 on both axes.
I'm needing the points to show the member name and xy coordinates in the popup when hovering over the point.
How do I accomplish this?
Here's some sample data.
MEMBER X Y JOE 397 397 JIM 421 384 JANE 467 406 JEFF 389 395
I am using the following code to delete all of the series in the chart. It is give type mismatch error.
Sub allchannelchart() Dim i As Integer, s As SeriesCollection ActiveSheet.ChartObjects("allchan").Activate 'ActiveChart.PlotArea.Select For Each s In ActiveChart.SeriesCollection s.Delete Next s
I have downloaded a file called "scroller.xls" under the section "Excel charts". The file was made by Andy Pope and shows how to make a graph dynamic by combining Named Ranges and a scroll bar.
My chart is actually XY scatter chart with data points connected with smooth lines. I followed but when I start scrolling, the data points (smooth lines) got "eaten up" as I move.
I tried line chart and it works perfectly.
Does scrollbar work for XY scatter chart? Is there a different formula for XY scatter chart?
And I want the chart to be in new sheet, but I couldn't put the scrollbar in new sheet. I would like to know if it is possible when the chart is located in new sheet because my chart is quite large.
I want to show a stock chart in Open-High-Low-CLose format, then I hope that a trend line can be displayed at the same chart, what can I do? It seems like that stock chart can't be merged with another chart
I would like to use multiple scatter plots and bar charts in one chart. Problem is that X-Axis goes horribly wrong every time, even if I have same points on both scatter and bar. Here is Data for 2 Scatter Plots
I start with Scatterplot and put those in the graph, then I add other data and change it to Bar Chart, but this is the point where X-Axis gets mixed up and scatter plot X data and Bar chart X data are not matching.
