Dynamic chart to represent % of 52-weeks high and low of a stock

I'm a Financial Analyst and (hence) an amateur excel user.

I want to have a chart in excel 2010 as part of my dashboard to represent a stock's 52-week high, 52-week low, and % of 52-week high and low.

I have seen this really innovative way of representing it, used on a stock exchange's website (images below)

Capture.JPG

Capture1.JPG

Low and High Prices with dates on either sides of a bar, along with a pointer at the relative position of current price.

Is there any way this can be created in excel?

Any other graphic way of representing this are also welcome. I just don't want to have a drab text box giving the information.

All help appreciated.

Hi

Is this what you are after?

I have entered the data as a random walk starting at 500, so pressing F9 will recalculate to illustrate different values

I assumes that the latest data are in the cell adjacent to the maximum value in the date column A

The chart is an XY scatter with the x values set at 1, 0, and the latest value as a percentage of the gap between the highest and lowest stock values

Hi Nicky!

This is exactly what i was looking for! Thanks a lot!!

Though i will directly pull the high and low prices off a database so i won't be needing the table on the left. But it taught me what i was trying to figure for a while - pulling the Date for a certain price from a similar table. You have done that using the offset function - which i will take a while to get my grip on

Thanks a ton for this!!!

Can this be done in a horizontal format???

Thanks for the help. Was searching here and there for this.