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.
Learn more about this technique is from KPI Dashboard Sorting article.
Download Stock Performance Analysis Workbook
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.
What about you? Do you like this technique? Are you planning to use it anywhere? Please share your ideas using comments.
12 Responses to “Analyzing Performance of Stocks using Excel [Example]”
Next step:
Automating the data feed!
Smart use of functions here!
at last you post something 4 me!
ha ha ha.
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?
Ooops... Sorry, just realised that RANK doesn't work for text!
@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.
your proposed formula : =RANK(J11,$J$11:$J$40,2-$I$3)
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)
)
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".
... next logical step to link the "suicide & murders" data to the stock performance data?
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)
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.
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.
[...] 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 [...]