If you don’t want to calculate compounded annual growth rate yourself, you can do it quickly and easily with this free calculator:
| Percent Change | |
| Number of Years |
[WP-Coder id="1"]
How to Add Stock Quotes Into Your Excel Spreadsheet
Unfortunately, there’s no Excel formula that can add stock quotes for 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 sorts of other data. And in this post, I’ll show you how to get the data from Google Sheets into Excel.
In essence, Google Sheets is your data source or database, and you’re going to import that into Excel. It’s not specific to stock quotes, but it’s an example of how you can accomplish the same thing. So first up, you want to create your file in Google Sheets using that earlier post as a guide. Here’s an excerpt of what my file looks like in Google Sheets:
Once you’re ready, it’s time to link your Excel file to that Google Sheets file, and here’s how to do that:
How to Link Google Sheets to Excel
- On the File menu, click on the button to Publish to the web
On the next screen, you should see something like this:
2. Select the tab that you want to export under the Link section and change Web page to Comma-separated values (.csv) and click on the Publish button, that will generate a URL:
3. Copy the URL that was generated in Step 2 and go back into Excel and under the Data tab click on the From Web button which is in the Get & Transform Data section
Paste it into the next screen’s URL field and click OK
On the next page you should see a preview of your data and if it looks okay then click on the Load button.
What you should see afterwards is what was on your Google Sheets tab from earlier:
And there you have it, your data from Google Sheets linked into Excel. If you make changes to your Google Sheets file, or if you want to refresh the stock quotes, right-click anywhere in the Excel sheet and select Refresh. Note that sometimes it may take some time before the file is updated on Google Sheets and before you’ll see any changes that you have made to the file.
It may not be an ideal solution if you’re looking to get stock quotes, but it gets the job done and avoids you having to try and find a complex formula or macro to pull the data that you want. You can use the Excel sheet with your Google Sheets data as a database and then lookup the stock prices from another sheet. The benefit of using Google Sheets is that you can have the best of both worlds – putting data online that you can easily update, and not be limited to Google Sheets and be able to edit and manipulate it as you need to in Excel.
Word of caution: if you delete or move around data in Google Sheets it could cause issues, especially if columns are missing and when you go to refresh it cannot find them anymore. If there is an error as a result of it or if you need to change the source, you’ll want to edit the query. When you click on the data in Excel you should see a section for Queries & Connections where you can edit the query. This is where you can select which data you want to include as well as change the source that you are pulling from. However, if it may be easier to just re-publish the data.
If you liked this post on How to Add Stock Quotes Into Your Excel Spreadsheet, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Use Google Sheets to Track Stock Prices and News
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:
=GOOGLEFINANCE(“GOOG”,”price”)
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:
=GOOGLEFINANCE(“GOOG”,”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:
=GOOGLEFINANCE(“GOOG”,”price”,”Jan 1, 2017″,today())
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:
=IMPORTFEED(“http://rss.cbc.ca/lineup/topstories.xml”,”items title”)
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:
=GOOGLETRANSLATE(B1,”en”,”zh-CN”)
My output looks like this:
So now you can pull news stories from your favorite news site (just figure out the rss link) and you can translate it into whatever language you want. Unfortunately I can’t tell the accuracy of the translation, ‘Simplified’ Chinese didn’t make it any simpler for me. I still can only make out CSIS from all of that translated text.