# Analyzing Performance of Stocks using Excel [Example]

Charts and Graphs - 12 comments

Last week, we learned how to visualize Suicides vs. Murders data using Interactive charts in Excel.

William, one of our readers, took this technique and applied it to Stocks. He emailed me because he has some formula issues with the stock data. Once I solved the problem, I asked him, “Can I share this with our readers?” and he is too happy to agree. So here we go.

### Interactive Analysis of Stocks using Excel

(Data as of 20SEP2011, 4PM EDT, from Yahoo Finance)

### How does this Work?

This workbook is just a clone of Suicides vs. Murders visualization with different data. The only change is that, here we used LARGE and MATCH formulas instead of COUNTIF formula to sort the list.

Click here to download the workbook & play with it. Just change the data & formulas in “Share Data” worksheet to modify this.

### Do you Like This?

I really liked how William put this together. It is simple and yet, quite powerful.

### More on Stock Market Analysis using Excel

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

Thank you and see you around.

### 12 Responses to “Analyzing Performance of Stocks using Excel [Example]”

1. Muntz says:

Next step:
Automating the data feed!

2. Smart use of functions here!

3. Hanif says:

at last you post something 4 me!
ha ha ha.

4. Johnny says:

2 columns K and L in "Share data" are used to find out the sort order.

But to get the same results, we only need 1 column with this formula (from L11 down):

=RANK(J11,\$J\$11:\$J\$40,2-\$I\$3)

Are there any disadvantages with this?

5. Johnny says:

Ooops... Sorry, just realised that RANK doesn't work for text!

6. Matth78 says:

@Johnny
I agree with you, we can resort to only one column.
For sorting text you have to do it the same way it's done in column K.

Become :
=IF(\$I\$2=1,
NB.IF(\$J\$11:\$J\$40,CHOOSE(\$I\$3,"")&J11)+1,
RANK(J11,\$J\$11:\$J\$40,2-\$I\$3)
)

7. Matth78 says:

I forget to add, IMHO, "share viewer" sheet should get sorted result directly with index and match. There's no need to do it in column N to U from "Share Data".

8. JimmyAG says:

... next logical step to link the "suicide & murders" data to the stock performance data?

9. gsvirdi says:

This sounds like a wonderful thing to keep track of stocks. But I'm nt able to get.... How to change the content of "share data" sheet? I mean how did u created that list in Yahoo finance? ..... I can't find any "Refresh" or "Fetch" button..... Is there any auto refresh function in the sheet to fetch latest quotes? What is the interval of refresh???? How to know if the Fetching was successful or not??? Lots of doubts here.... (as a user)

10. RP says:

I need a solution to reporting 9 projects with three heads submission status, collection status and pending. I can create a pivot and data for one month, but How do i report progress through a chart it bi-weekly? I need a impressive way of putting it in my report. Please help.

11. RL says:

How can you refresh this data. Is there an API that can be put in place to refresh this data in real time. We can then use it to conduct technical analysis for the particular stock.

12. [...] very nice excel sheet example - Analyzing Performance of Stocks using Excel Analyzing Performance of Stocks using Excel [Example] | Chandoo.org - Learn Microsoft Excel Online This chandoo guy is very good in excel. He shares many useful [...]

 « Refresh All Pivot Tables at once [Quick Tip] Sum of Values Between 2 Dates [Excel Formulas] »