fbpx
Search
Close this search box.

Analyzing Performance of Stocks using Excel [Example]

Share

Facebook
Twitter
LinkedIn

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

Stock Performance Analysis 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.

More on Stock Market Analysis using Excel

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

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

  1. Muntz says:

    Next step:
    Automating the data feed!

  2. Hanif says:

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

  3. 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?

  4. Johnny says:

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

  5. 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.

    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)
    )

  6. 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".

  7. JimmyAG says:

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

  8. 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)

  9. 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.

  10. 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.

  11. [...] 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 [...]

Leave a Reply