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.

About Botsplash

Botsplash, is an innovative, digital messaging software with the ability to connect agents and customers across any digital platform. In order to win and keep a customer’s business, businesses must be able to connect with customers in a meaningful way using websites, social media, text and email. Botsplash helps businesses adopt digital strategy with right balance on Live Chat and Automation.

For inquires, contact us at (704) 445-3362 or [email protected].