One of the advantages of using Google Sheets over Excel is that it is easier to access live, dynamic data that you can access from any device that can install the app.
Pulling Stock Quotes
A great feature of Google Sheets is that you can easily pull stock prices (delayed) from Google Finance. There is a unique function called GOOGLEFINANCE that can pull any of the following stock details including price (including open, high, low), volume, even the last time it traded. If I wanted to pull Alphabet’s stock price I could use the following formula:
That will pull me the most recent stock price. If I wanted to see the percent change since the last day’s close I would just change price to changepct:
If you access the help you will see a list of more options:
But you can go even further than that, pulling multiple dates at a time. For example, if I wanted all the closing prices since the start of the year I would enter the following formula:
In Google Sheets it automatically creates a table of values for you and you don’t have to worry about making an array like you would in Excel. The result of the above formula looks like this:
I only entered the formula in cell A1 and it produced the list of results. You can also select an interval if you don’t want every day in the range to show a total.
Getting News Feeds Using RSS
Another unique function of Google Sheets is you can pull news feeds from your favorite news site using the IMPORTFEED function. The key thing is you need to find the rss feed of the news site you want. Finding this is as easy as typing the name of the news feed you want and rss after it. For example, the the list of all of CBC’s rss feeds are found on http://www.cbc.ca/rss/index.html. I can use the top stories rss feed of http://rss.cbc.ca/lineup/topstories.xml for my feed.
My formula in looks as follows:
By using “items title” it will only pull the title of the story, which is a bit neater and easier to look at as the titles do not take up as much space as the descriptions as well. If I selected “items” then I would get five columns of data – title, author, link, date, and the description.
Instead, what I can do is in the next column over enter the same formula and select “items url” which will now have the story and the related link next to each other. This way I can pick and choose what I want. This is how it would look:
I have shrunk down column A since I didn’t want the whole url to show.
I now have all the pieces to make a start page using nothing more than a spreadsheet:
Perhaps it doesn’t rival MSN or Google’s home page but it works for me. I’ve made the formulas for the stock calculations relevant to the cells in column B so I can change the ticker symbol as I want to. The main benefit with using this is 1) you don’t need to open a browser to get stock quotes or news, and 2) you can easily access this information from your phone, all you need is the Google Sheets app installed.
But wait, that’s not all!
Translating Text
I’m not sure why Google felt the need to, but you can even use their translator function as well inside of Google Sheets using the GOOGLETRANSLATE function.
What I could do is translate these news articles. You need to know the two character code for the language, to get that you can find it on this website:
https://sites.google.com/site/tomihasa/google-language-codes
So what I am going to do is translate the news headlines I pulled earlier and translate them into Chinese. Google has two language codes for Chinese – Simplified, and Traditional. I’ll go with simplified, which is zh-CN.
My formula looks like this:
My output looks like this:
[…] you. However, there is a workaround for that which can help you get what you’re after. In a previous post, I covered how to pull stock quotes using Google Sheets which is able to pull in prices and all […]