Excel Dynamic Chart #13: Web Query Stock Percent of Target Chart

Hope everyone is well. I turn to your expertise for assistance with the following two items - any and all help will be greatly appreciated.

I'm trying to create a dynamic stock chart that returns only the 120 most current values in column(D). D1 is the header, and D2:D6 are empty due to adjacent data-sensitive formulas. Data formally begins at D7 and ends at D759. The desired chart series would initially be D:640,D:759. What would be the necessary steps / formula to create a dynamic chart that would continue to 'roll' as new daily-data is entered?

Finally, using the same data range requirements, how would I have H2 for instance, continually calculate the Average of this dynamic series?

The attached file contains all the above information, and any I may have omitted. Thanks for your time and any help.

-Chris

Hello,
I would be grateful if someone could help me create formulas to determine how much sales must happen going forward (for the month, or for the quarter or for the year) based on YTD, Week-to-date or Quarter-to-date actual sales and a target for the year, the month, and the quarter (simple monthly average are fine, e.g. forward sales divided by remaining periods).

I have tried I cannot get a mix of actuals and estimates (for my chart). I have a hard time making Excel calculate the remaining (monthly, or quarterly, or annual) estimate(s) and replace the last estimate(s) (in the data to chart) with the new actuals when they are entered in a sales database (and only keep the estimates when there are no actuals available).

I have tried to set up columns to calculate cumulative data, but my spreadsheet got only bigger and I got more confused. Thanks for your help!
(I am sorry if the spreadsheet below looks ugly, I didn't know how to import a clean excel file on here)
Thanks a lot in advance!

1 B C D E F G H I
2 Sale Database






3 Month Week # Sales





4 January Week 1 $9,000
February Target = $37,000

5 January Week 2 $8,180





6 January Week 3 $9,700





7 January Week 4 $8,680





8 February Week 1 $7,600





9 February Week 2 Manually entered every week


10 February Week 3


11 February Week 4


12








13 Question: Create a formula to estimate Week 2, 3, and 4 given the February target
14 In this case, $37,000 minus $7,600 = $29,400 can be spread evenly over 3 weeks
15 Then when the actual sales for Feb. Week 2 are in, I want only week 3, and 4 to be estimated
16 For example, February sales = $8,000




17 How do I write this formula to estimate forward sales as I add new data?

18








19
Before I entered Data in database



20
Source Data for Graph





21
February Week 1 $7,600 (note: actual)


22
February Week 2 9800 (estimate)



23
February Week 3 9800 (estimate)



24
February Week 4 9800 (estimate)



25








26
After I entered Data in database



27
Source Data for Graph




28
February Week 1 $7,600 Actual



29
February Week 2 $8,000 (estimate)



30
February Week 3 $10,700 (estimate)



31
February Week 4 $10,700 (estimate)



Also, I'd like to estimate remaining Quarter or Year sales, based on Qtr and Yr targets
(same principle as above

I'd like to be able to to adjust bar widths on an open-high-low-close stock chart (AKA: candle chart). This is possible to do on a column chart, but I'm not able to find a way of doing it on this particular chart.

To bring this chart up, first paste the following example data into your spreadsheet and select all of it:

111.20 111.74 109.09 109.21
113.92 114.27 111.56 111.70
114.28 114.45 112.98 113.89
113.62 115.13 113.59 115.06
114.73 114.84 113.20 113.64
114.49 115.14 114.42 114.93
113.95 114.94 113.37 114.62
113.97 114.21 113.22 113.66
115.08 115.13 114.24 114.73

Then:

insert -> other charts -> all charts -> stock chart -> select second one from the left.
it should read "open-high-low-close" when you hover over it's icon.

What I'd like to do is make the bars 50% wider or so. If it is not possible to do it with standard excel commands, is there a way to program this with basic?

Thank you.

Hi,

I'm using Excel 2003.
I have attached a dummy workbook.

I would like to create a rolling line graph based on the data in Rows 3, 7, 9, 11 (highlighted orange for ease) showing the next 12 months worth of data based on month in A1.

I have tried to amend previous posts for Dynamic charts but i cannot seem to get it to work.

If anyone can help me out that would be fantastic.

Many thanks

Hello all, this is my first post, I hope someone can help me, my thanks in advance for reading.

I would like to create a chart with a similar concept to the typical stock chart you see on google finance - eg http://tinyurl.com/2fp5dnq (note, this is completely different to what a stock chart is represented as by excel - eg candlestick chart)

I would like to have data represented by multiple lines in addition to probably just one bar chart at the bottom. The same way line=price and bar=volume on the chart above.

I am surprised this functionality is not available in Excel 2007 or 2010. Anyway...

I have attached a screen shot of how I would like it to look.

Appreciate any help. Thanks.....Callum

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.

i have the data for open high low and close, as well as the moving average values. i want to put the ma lines on to the stock chart, how can i do it?

thank you

Hi,

I know how to change a data series so it can be a line graph. This is great if the target is a constant. I have a graph that has 4 columns of data (each with the same 3 series of bars). For example;

The 3 series are - Target, Actual, Planned
The 4 columns are - Quarter 1, Quarter 2, Quarter 3 and Quarter 4

The target for Q1 might be 50, Q2 100, Q3 150 and Q4 200. I want a line to only go through the Actual and Planned columns for that Quarter (i.e. not a line that goes up and down across the graph). As it is, I could draw a line however the target figure can (and knowing my luck, probably will) change from time to time.

Anyone with any ideas would be greatly appreciated.


Thanks,

Ineedcoffee

When I add a new series to an Excel stock chart, the added series has stretched some of the high-low lines of the stock H-L-C bars. (they are stretched so as to meet the value of the new series)

Bob Peltier describes on a webpage how to deal with this for some circumstances. However, the example he shows to create a chart with H -L-C bars with an added series is not detailed well on this webpage so I don't understand what he did to get the H-L-C chart with the Index that he shows. It is not clear to me how to fix the problem. The webpage is:

http://peltiertech.com/Excel/Charts/StockChartPlus.html

Maybe I missed something.

Can anyone in this forum offer a suggestion?

Thanks,

Art

how to chart stock and line in a chart.

in excel, i only can chart a stock, but i also want to chart stock including line in a chart, would you like to give a better solution?