Keep track of your Investments+Earnings with Excel

So, Excel is one of those programs which is not utilized nearly as much as it should be by the general public. It's extremely powerful and offers you tons of useful functions and ways to grab, manipulate and display data so as to best benefit you.


Requirements to get started:
Microsoft Excel OR Google Doc Spreadsheet (I'll be using the latter for this tutorial. You are able to do the same in Excel however using the "Data" tab and importing from a website)


Here's what the end result may look like(Though of course, you are free to format things to your liking, not use conditional coloring, etc.)


So basically what is being shown is the PCTChange of the given stocks, along with the current last-traded price of the stock. It is then color-coded (Red/Green) depending on if it has a negative percent change, or a positive. (D37-D43)
The current price is then taken(B37-B43) and multiplied by the number of shares I own, where it displays the value of my shares next to "Stock Worth" and under the corresponding stock(A34-H34)
Kind of cool right? I think so!

Getting Started:

So, let's start off with something everyone knows about GOOGLE, Google's Ticker Symbol is GOOG. https://www.google.com/finance?q=GOOG&ei...Gc-msQfYHg for reference.
Now; we're going to go to our spreadsheet and import the data we want from that page. There's two different ways to do this, you can decide which format you like better depending on how much information you're looking for.

Code:

=GoogleFinance(Ticker, attribute)

Or

Code:

=ImportHTML("website";"table";index)

I prefer ImportHTML as it displays more information easily; but that's just me. You are free to use GoogleFinance if you want, I'll be showing you how to use both.

IMPORTHTML

If you're going to use ImportHTML you'll want to find your stock on Google Finance(We'll be using GOOG here as our stock too) then navigate to the left and hit the "news" section. Copy the URL (https://www.google.com/finance/company_n...ojwsgfKkQE)

Then go back to your excel and put it in the ImportHTML Function
Ex:

Code:

=ImportHTML( "https://www.google.com/finance/company_news?q=NASDAQ%3AGOOG&ei=pP_8UtDhOLSIsgedew"; "table" ; 1)

It should then display this!


Now, that may look like random numbers but each column there has a meaning!
First is the ticker symbol of which you're retrieving the data from:
Next is the current share-price
After that one is the percent change along with share-price change
Following that is the low for the day
Then the share-price change by itself
Then of course is the PCTchange by itself
And lastly is the data-delay for retrieving this information.



Cool right? Now you can display more information than this as well using the Import HTML function such as the Range, 52 Week, Open, VOL/Avg, Mkt Cap and P/E. To do this just replace your IMPORTHTML website with the summary link
Ex:
Code:

Code:

=ImportHTML( "https://www.google.com/finance?q=NASDAQ%3AGOOG&ei=pv_8UoHqMIjwsgfKkQE"; "table" ; 1)

GOOGLEFINANCE

Now, maybe you don't want all that info, instead you want only certain specific things displayed about your stocks. That's cool too!
This is also made super simple using

Code:

=GoogleFinance

So say I only wanted to display the current share-price of a stock, all I would have to do is type in

Code:

=GoogleFinance("GOOG", "price")

and it would display something like this:
The basic format for this function is

Code:

=GoogleFinance("Tickersymbol", "attribute")

You can find a full list of attributes here https://support.google.com/drive/answer/3093281

CONDITIONAL FORMATTING

Hopefully now you've got all the information you're interested in seeing being displayed for your tickers. But how's about some color so we can really quickly tell if we're in the black or not.

You're going to want to select the cell you're interested in, go to the top and under the "Text Color"
or the "Fill Color" you're going to want to click "Conditional Formatting" it will then display something like this

This is fairly self-explanatory but I'll take you through it regardless. On the left where it says "Text Contains" you can select a few different things
Select the one you're interested in and then input the condition to make it change.
For example, if I wanted the PCTChange to be green if it's positive I could use the "Text Contains" option and simply put a "+" sign in the text box, and then select the color of my choice.


You can of course add more rules as well by selecting the "Add Another Rule" button at the bottom.

After setting your conditions hit the "Save Rules" button and take a look!

YOU'RE DONE!

Congratulations! You're done! You should now have all the tools at your disposal to make a pretty damn-good looking spreadsheet that contains all the info you need about your stocks. If you've any questions, feel free to post and I'll do my best to help you, or if anything is unclear and you would like me to clarify let me know!


**IMPORTANT NOTE** The tickers and information displayed is to provide visual examples only, I am not suggesting these are my actual holdings, investments, or anything else. I am also not trying to sell, or influence the reader's opinion of these stocks by displaying this information.

Very nice quality of thread, thanks for the post.