Stock tracker app with MS Excel

For a very long time I felt excel for a robust tool for crunching numbers, comparing data and building pretty charts. Until about a few years ago I met a person who used excel for so many different every day uses – access reddit feed was a crazy one that I saw. After that day I wondered how I use explore and build something useful to me. I love keeping track of stocks and check their performance. So decided to build an app to monitor stocks. It was piece of cake, 30mins and ready!

Here are the steps and excel formulae – Have fun!

1. Columns A to E are input values that are already known -> Stock ticker, Number of stocks purchased, Purchase date and Purchase Price of Stock.
2. Column F is calculation on Number of Stocks * Price per stock at time of purchase -> =E2*B2
3. Access any finance website that has API to return stock price. Here I connected to yahoo webservice -> http://download.finance.yahoo.com/d/quotes.csv?s=TSLA&f=l1
4. Formulae use in excel ( this is for TSLA) -> =NUMBERVALUE(WEBSERVICE(“http://download.finance.yahoo.com/d/quotes.csv?s=“&A2&”&f=l1”)) .

Yahoo Webservice call for Current Stock price

5. Column H is calculation on Number of Stocks * Price per stock now -> =G2*B2

6. Column I ( Periods) is time since stock purchase -> =YEAR(TODAY())-YEAR(D2). Not granular by months, since I’m usually interested in longterm stocks.

7. Column J is displaying the percent change to indicate gain/loss

Showing calculation to compute % gain/loss

8. Row 14 is Purchase Price and Current value Totals, along with the different to indicate $ value of investment.

Is it not surprisingly simple? Try it out and explore other ideas. Excel is a great tool, put it to use in different ways.