How to track stocks with google sheets

Build your own all-powerful stock tracker

Are you a stock trader with a messy spreadsheet? Well, I was one too.

I have been using Google Sheets for a while now to track the performances of various stocks. What started off as a small clutter of stock research soon started growing too large for manual updates, forcing me to automate the data. Here’s how I did it.

Initially, I had filled in the data for the stocks manually, by Googling metrics for the various stock tickers and pasting the information in.

Google beautifully displayed every single metric I wanted to know. Of course, this method of manually inputting data soon became exhausting as the number of stocks I tracked increased in number.

And then I realised that Google Sheets supported a cell formula called GOOGLEFINANCE . This seemed like a great built-in solution and I started to dynamically fetch the values in my spreadsheet. The beauty of the formula is that I could dynamically fetch the most important metric for all my stocks – the current price!

Armed with this new formula, I created the following spreadsheet:

In this spreadsheet, Google Finance automatically queries the current price of the stock based on the “Ticker” column. I have used both the exchange symbol (e.g. NASDAQ) and the ticker symbol (e.g. AAPL) to get the most accurate data from Google — i.e. GOOGLEFINANCE(“NASDAQ:AAPL”) . For example, here, B2 in the formula refers to the cell containing NASDAQ:AMZN .

I had used various columns to track the stocks. The only manual fields in this spreadsheet are the stock name, current quantity and average price purchased. The rest of the fields are dynamically updated based on the current price.

Google Finance also supports more metrics such as the market cap, PE ratio, Earnings Per Share (EPS) etc. A full list of supported metrics by GOOGLEFINANCE can be found here:

I realised price may not be the only metric that is useful to me. What about the amount of dividends a stock pays? And in the case of Exchange Traded Funds (ETFs), I also want to know the expense ratio of the fund — which is the recurring annual fee that ETFs charge their shareholders.

And this time, I happily went back to Google Finance to check for the expense ratio and dividend results.

After searching high and low, and looking online for answers, I realised Google Finance only provides the basic information. This was a problem as I really did not want to resort to manually entering data into my portfolio tracker again.

However, Google offered a cell formula called IMPORTXML . According to Google Docs Editor Help, this function:

Imports data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.

With many websites such as Bloomberg and Yahoo Finance providing real-time finance data for stocks, this means that we can scrape the data off their pages into the spreadsheet!

I decided to go with Yahoo Finance as I had already been using it for a while. Now all I had to do was to somehow capture the data for the dividends (or other metrics) from the stock’s page into the spreadsheet dynamically.

Looking at the IMPORTXML function, we need to provide a web URL, followed by something calling the xpath_query . Finding the xpath_query was not too difficult. Using Google Chrome, we need to right click on the metric we want — in this case, the dividend and yield. And click on “Inspect” from the dropdown menu.

What comes up next is a bunch of code, with one line highlighted. We need to right-click on this again, choose “Copy” and choose “Copy XPath”.

This gives us a weird-looking string:

Now, we can write our IMPORTXML function like this:

Note that I swapped the double quotes for “quote-summary” to single quotes, ‘quote-summary’. This is to avoid conflict with Google Sheet’s formula format.

This nicely fetches the data we want — the dividend and yield: “3.28 (0.93%)”.

Next step, is to split the data into the actual dividend and the yield into different columns. To do this, we can use the SPLIT formula in Google Sheets. To keep the formula lean, I extracted out the XPath from above into a separate cell, B9 in this case.

We have use split twice:

1. To split the dividend and yield into 2 different columns
2. To split our ticker NASDAQ:AAPL into just AAPL since that’s what the Yahoo Finance URL requires (e.g. ). Splitting this gives us NASDAQ and AAPL . We use the INDEX function to only take the second item which is AAPL .

Our split formula will look like this now, where B3 is our cell containing the ticker NASDAQ:AAPL .

One of the lesser-known functions in Google Sheets is GOOGLEFINANCE, which allows you to track current or historical financial securities data on the stock market. Here’s how to use it.

What is Google Finance?

Finance is Google’s real-time tool that displays current market information and aggregates business news. It’s currently integrated with Google Search, so if you look up the ticker symbol of a specific corporation on Google such as WMT for Walmart or AAPL for Apple, you will immediately see the current stock quote and historical data for that security. You can click one of these stocks to go to a company’s Google Finance page, which shows the company’s financials, related news, and allows you to compare it with other commodities.

While there are other, more robust tools you can use to track securities, Google Finance is the only one that can effectively integrate with Google Sheets. Whether you’re a novice to stocks or an experienced trader, this integration is the easiest way to import and monitor stock data in a spreadsheet.

By the way, Google Finance’s sheets integration is only available in English and doesn’t include most international exchanges yet. So if you want to transact on Asian or European exchanges, this may not be the best option for you.

The Google Finance Function

The function that pulls stock data is called “GOOGLEFINANCE.” The syntax of the function is quite simple and uses five arguments, four of which are optional.

The first argument is the ticker symbol. These are codes that companies have when they’re listed on the stock market, such as GOOG for Google or BAC for Bank of America. You can also specify the stock exchange that your chosen stock is listed in to avoid discrepancies. Since Bank of America is listed on the New York Stock Exchange, you’d type “NYSE:BAC.”

To get the ticker codes and exchanges of you’re desired stocks, you’ll have to do some research. You can look for them on Google Finance or your chosen portfolio management tool.

The second argument is the attribute you’d like to display. By default, it’s set to “price” if you leave it blank. Here are a few of the attributes you can pull out using the function:

• price: The price of the specific stock in real-time.
• volume: The current trading volume.
• high: The current or chosen day’s high price.
• low: The current or chosen day’s low price.
• volumeavg: The average trading daily trading volume.
• pe: The price to earnings ratio.
• eps: The earnings per share.

Take note that the attributes you can display vary on whether you’re using current or historical data. Here’s a full list of attributes that you can use for the argument. It’s important to remember that current data refreshes every 15 minutes, so it’s not completely real-time.

The third argument is the start date, which only applies if you’re using historical data. You can type “TODAY()” or leave it blank to display real-time data. The fourth argument specifies either the end date or the number of days from the start date. If left blank, the function will return the data from a single day.

The last argument is the interval, which allows you to specify the frequency of the data. You can set it to “DAILY” or “WEEKLY.”

One thing to note is that Google Sheets processes the ticker symbol and attribute arguments as text, so make sure to place quotation marks around them, or you will get an error.

Stock Tracking in Action

For this example, let’s say you want to look up the current price of a stock of Facebook. Facebook is listed on NASDAQ with the ticker code FB. In this case, you’ll type down the first argument as “NASDAQ:FB” along with “price” as the attribute. So the formula for this would be =GOOGLEFINANCE(“NASDAQ:FB”,”price”) .

If you want to view the daily closing prices for a particular week, such as the week of October 15, 2018, you will specify that date range in the third and fourth arguments. The code for that becomes =GOOGLEFINANCE(“NASDAQ:FB”,”price”,DATE(2018,10,15),DATE(2018,10,20)) . Take note that viewing historical data expands the generated information into array data, which takes up nearby cells.

You can also use the function to generate data for a list of stocks automatically. Just type out the tickers in a column, then use the cells in your first argument. Since our ticker code is in cell C4, you’d type out =GOOGLEFINANCE(C4,”price”) . Below is a list of stocks with their corresponding current prices.

If you want to track a list of attributes, you can type them out in separate cells like in the above image. Then, you can link the second argument to the cell with the attribute’s name. For the price cell for NYSE:IBM in the example below, the formula would be =GOOGLEFINANCE(C$2,$B5) .

Maximizing Google Sheets

The best part of having your stocks on Google Sheets is that you can use various data manipulation tools on them.

For example, let’s say you want to use Google Sheets to keep track of the value of all of your financial assets, such as your stocks, savings accounts, time deposits, and more. Using Finance, the price of your stocks will update in real-time, so you get a full picture of your position at any given time.

Converting Currency with Sheets

Another great function of Google Sheets is that it can convert currencies in real-time. You can do this by typing the stock ticker “CURRENCY:” followed by the codes of the two currencies you want to convert, such as “USDGBP” or “EURJPY.” You can also display historical currency data by specifying a date.

For example, if you’re living in Europe and you want to convert some USD into the Euro, you’d type =GOOGLEFINANCE(“CURRENCY:USDEUR”) and multiply that number with the amount of USD you’re converting.

This has a lot of great use-cases besides foreign exchange trading. For example, if your line of work involves getting paid in a different currency, you can set up an invoice that automatically converts the payments you receive to your home currency.

About two months ago I created a spreadsheet in Microsoft Excel to track my dividend stocks and income. Not only did I create quite the handy spreadsheet, I also shared the end result with you guys so you could all take advantage of it. Since reactions were quite positive overall, I left it at that for a while.

However, one major beef most of you had, was the fact that it takes a lot of manual actions to keep track of everything. As a result, I decided to try out Google Sheets, which allows for easier automation. After a couple hours of finicking around with Google’s version of Excel, I’m happy to share my now mostly automated dividend tracker!

Just like last time my spreadsheet is aimed at international investors. Being one myself I need to be able to track my investments in multiple currencies, mainly to keep an eye on the capital appreciation. Following the growth of my dividend income stream is what it’s all about, but it’s nice to know the actual value of your portfolio at any given time in your home currency too.

Since the basics of my spreadsheet haven’t changed much, the workflow remains rather the same. On the “Portfolio” tab you’ll have to enter a stock’s name, ticker, country, sector and currency. After making a purchase you’ll of course also have to provide the amount of shares bought and the cost basis in your home currency. Google will take care of the rest for you.

Currency exchange

The top left of the dividend tracker still shows a currency exchange calculator and a pie graph to represent the weight of each currency in your portfolio. You can change the currencies you invest in to whatever you like – if you’re up for some Zimbabwean companies, go for it! Contrary to last time, Google now provides the last known exchange rate automatically using the following function:

Like I already said, you should enter the right currency symbol in your list of stocks in column E, otherwise Google Sheets will display an inaccurate portfolio value. Columns I and J on the same tab then do the rest to provide you the value of your holdings in your home currency.

Sector diversification

My dividend tracker also displays the diversification of your portfolio based on the input you provide in column D of the “Portfolio” tab. Since most people found this a useful and easy way to visualize the different industries they were invested in, I haven’t changed anything here. The spreadsheet still aggregates the home value of every individual stock in column J based on the sectors in column D.

Year-Over-Year performance

A brand-new function, and one which I really like myself, now displays the past income performance of your portfolio on a yearly basis. A small graph quickly visualises your progress for the year compared to the previous three years, as you can see in the picture below. The data needed for these statistics comes from the “Dividend income” tab, which I’ll explain in detail below.

Dividend overview

The “Dividend overview” tab provides you with a detailed table and graphs of your dividend income on a per position and per month basis. Nothing has changed here compared to the previous version of my spreadsheet. Users of my budget tracker will also be familiar with the mechanic behind this sheet. Cell O1 is based on cell Q1 on the “Portfolio” tab, which then collects all dividends from a particular year from the “Income” tab.

Like last time, the only thing you have to do to make this sheet work, is add a ticker when you buy into a new position and add that position to the “Portfolio” tab. When your dividends start rolling in you enter those on the “Income” sheet by date and ticker. The dividends entered there will also be displayed on the first page, both in the table and the YOY performance overview.

When a year passes by all you have to do is change cell Q1 to the new year and Google Sheets will update the dividend tracker. The upside of doing it this way is that you’ll be able to go back in time for a detailed overview from any of the previous years by changing the year up top again.

Further automation

If you want to automate tracking your investments and dividends even more, you could have Google Sheets insert the dividend payments automatically. Doing so, however, is rather convoluted. I haven’t added it to my spreadsheet for two reasons.

First, Google Sheets doesn’t offer a way to insert the dividend or yield through the GoogleFinance-function, so you’ll have to rely on third-party solutions like this one:

The formula above tries to download the dividend and yield of Johnson and Johnson (JNJ) from the Google Finance website, which actually works great. The major downside is that Google Finance often is wrong, especially for non-US stocks or stocks listed on multiple stock exchanges under the same ticker. The chance of wrong data in your dividend tracker is just too high. Another major downside is that you won’t be able to keep historic records.

Second, and a direct result of the previous problem, I’m keeping track of my net dividends; not the gross payments the companies make. Since I have to deal with both foreign withholding taxes and national taxation on dividend payments, it’s nearly impossible to automate the entire thing. Maybe that’s not the case for you, so you could actually implement the formula above if you wished to. If you decide to do so, take a look at Tawcan’s guide for implementation ideas.

Final words

If you’ve gotten this far, you’re probably wondering when the damned download button will finally pop up! Don’t worry, here it is!

I’ll provide you guys with an updated Microsoft Excel version without the nice automation Google Sheets provides and a Google Sheets variant, which obviously does almost everything for you. If you’re looking for a real life example, the Google Sheets dividend tracker is actually the last version of my own spreadsheet.

As always, feel free to play around with the spreadsheets and adjust things to your liking. I sincerely hope some of you find them useful, especially the automated one since many of you sent me an e-mail asking for automated solutions. Let me know what you think of it and if there’s something you’d like me to add.

Written by Valentine Schelstraete

Spreadsheets give us a place to store data but they also provide us with many analytical and graphical tools to identify trends. Most spreadsheets contain dynamic data that changes according to the inputs, formulas, and external data sources. Google Sheets updates in real time, giving all collaborators instant access to the latest data and resolving the problem of out-of-date spreadsheet versions. But when it comes to tracking trends for business analysis, you need to be able to see how, when and where your data changed. Saving historical data is the best way to track changes in Google Sheets. Here’s how to do it:

Why record historical values?

There are multiple reasons why you might need to track changes in Google Sheets. These include:

• Preserving laboratory readings, specific data entries or timestamps
• Monitoring fluctuating data like currencies and stock markets
• Tracking KPIs

Track changes in Google Sheets with multiple users

Google Sheets’ sharing function allows multiple people to edit the same spreadsheet. This is powerful for team collaboration but it can be a problem if you want to check and compare data with previous values. You can open the Version history to see who made specific changes to the spreadsheet, but to carry out detailed trend analysis, you need to create a record of historical data.

How to record historical changes to your data

The best solution to this problem is to create a Google Sheets workflow. Instead of entering, processing and storing all your data in one file, a workflow keeps dynamic data safely separated and allows you create a log of historical changes in one central master sheet. This enables you to

• Keep a log of data changes at specific time intervals
• Save older versions of your data without copy-pasting to a new sheet
• Automate the entire data recording process
• Generate dashboards and charts to analyze trends over time

Here’s an example:

Let’s say I am a stockbroker. I hold shares in a few companies and want to keep track of their stock prices. I track daily stock prices in this spreadsheet:

I want to analyze how these stocks perform over a period of time so I need to store these values on a regular basis. Instead of manually copy-pasting variables every day, I create an automated workflow to carry out this task. Here’s how:

Track changes in Google Sheets automatically

Use the Sheetgo add-on to transfer data between Google Sheets automatically. Select a time interval for updates, and apply the Append function. This will add the new data entry beneath previous entries, in a historical log.

In this example, data from my Stock prices today file flows into my Stock prices – historic data file — once a day.

Step 1) Start Sheetgo and create a connection

Install the Sheetgo add-on for Google Sheets

When you install the add-on, a new Google Sheets file will open automatically. This is your destination file, where you will create your historical data log. It will act like a master sheet, pulling dynamic data from your source spreadsheet at regular intervals and appending a list of data.

Give the file an appropriate name. In this example: Stock prices – historic data.

To open the add-on, go to Add-ons > Sheetgo > Start.

Sheetgo opens as a sidebar on the right side of your screen. Click +Import data.

Step 2) Select your data source

Under Select source, find the file you want to regularly import data from. This is your source file. Sheetgo displays your most recently accessed files in Google Drive. If you can’t see the file, use the search icon.

Now select the source tab. This is the tab/sheet containing the data you want to import.

Make sure that this tab only contains the data variable(s) that you want to record.

In this example, our data source file is Stock prices today and the source tab is Prices today.

Step 3) Append and automate

In the Settings section, enable Append to record historic values. A new data entry will appear in your destination sheet every time the connection is updated.

Under Automatic updates, choose how frequently you want the data to be imported and at what time of day.

In this example, I want updates once a day so I select Daily at midnight.

Think about how frequently your data changes and schedule automatic updates accordingly. If the updates are scheduled more frequently than the data changes, you may end up with repeated values in your historical data log.

Click Save connection to create the connection.

Here’s my destination file: Stock prices – historic data. Sheetgo has automatically created a new tab containing data from my source file. Note that the source file remains intact.

A few days later, my Stock prices – historic data file looks like this:

Fresh data is appended below the existing data entries every day. Once the connection is created, you can track changes in Google Sheets automatically. You don’t even need to open a spreadsheet. To adjust the update schedule or switch off automatic updates, just open the add-on and change the settings.

More automation in Google Sheets

As well as tracking historical changes, Sheetgo connections can be used to filter information or combine data from multiple Google Sheets into one central master file. If you regularly gather data from several files and copy-paste data manually, check out our article on how to automatically merge data from multiple Google Sheets into one.

I brought this post out of the archives due to a number of recent emails about how to track stock holdings. В I still use Google spreadsheets В today to track my dividend holdings.

With the market volatility, there is a heightened interest in dividend stocks and how to buy stocks in general. Within my article on when to buy dividend stocks, I mentioned that using a spreadsheet to create a watch list is particularly useful.

While not everyone has Excel to use their stock quote addin, most have access to Google’s free spreadsheet app. What’s neat about Google Spreadsheets is that it has a built in stock quote function that can be quite useful. The function can pull important semi real-time metrics like current stock price, price earnings, 52 week high/low, volume, eps, and market cap (complete list below).

How do you use Google Spreadsheets to create a stock watch list?

1. Syntax

Lets start with the code required to get this up and running. В Pick a cell on the spreadsheet, and use the following syntax.

=GoogleFinance(“symbol”; “attribute”);
where “symbol” represents the stock symbol of the company or mutual fund you’re looking for (like GOOG, QQQ, XXXXX, and so on), and “attribute” represents the type of market data that you want (like price, volume, and so on). If the attribute is left blank, price is returned by default.

Here is a complete list of attributes:

• price: market price of the stock – delayed by up to 20 minutes.
• priceopen: the opening price of the stock for the current day.
• high: the highest price the stock traded for the current day.
• low: the lowest price the stock traded for the current day.
• volume: number of shares traded of this stock for the current day.
• marketcap: the market cap of the stock.
• tradetime: the last time the stock traded.
• datadelay: the delay in the data presented for this stock using the googleFinance() function.
• volumeavg: the average volume for this stock.
• pe: the Price-to-Earnings ratio for this stock.
• eps: the earnings-per-share for this stock.
• high52: the 52-week high for this stock.
• low52: the 52-week low for this stock.
• change: the change in the price of this stock since yesterday’s market close.
• beta: the beta value of this stock.
• changepct: the percentage change in the price of this stock since yesterday’s close.
• closeyest: yesterday’s closing price of this stock.
• shares: the number of shares outstanding of this stock.
• currency: the currency in which this stock is traded.

Here are a few examples:

To insert the current price of Royal Bank (TSX) stock:
=GoogleFinance(“RY.TO”; “price”)

To insert the current voloume of Google stock:
=GoogleFinance(“GOOG”; “volume”)

Alternatively, the stock symbol and/or the attribute values can come from spreadsheet cells. For example, the function can be:
=GoogleFinance(A2; B1)

In this case, the attribute specified as a string in cell B1 would be returned for the stock symbol in cell A2.

2. Sample Stock Watchlist

Here is a sample spreadsheet that is similar to what I use to watch my favorite dividend stocks. В I use the full table below for each position, I used Royal Bank in the example below.

Hopefully the table is relatively intuitive, the confusing parts may be where it says “manual entry”. В Since Google cannot pull data for the annual dividend, you’ll need to obtain that info yourself. В You can generally get this information directly from the company website, or even other stock info websites (stockhouse.com, google finance, msn, dividendinvestors.ca).

The B7 manual entry field is the dividend yield that you would find attractive enough for you to buy. В For example, if you would buy RY.TO when it has a 4% yield, then put 4% in that field. В One tip, make sure that field (B6 and B7) is designated/formatted as a “percentage”.

One final thing, B8 is a cell that has a fancy “IF” statement. В For you non-programmers out there, it basically translates, (if current yield is greater than my desired buy yield, then show “buy”, otherwise, show “watch”).

What do you use for your stock/ETF watch list?

One of the lesser-known functions in Google Sheets is GOOGLEFINANCE, which allows you to track current or historical financial securities data on the stock market. Here’s how to use it.

What is Google Finance?

Finance is Google’s real-time tool that displays current market information and aggregates business news. It’s currently integrated with Google Search, so if you look up the ticker symbol of a specific corporation on Google such as WMT for Walmart or AAPL for Apple, you will immediately see the current stock quote and historical data for that security. You can click one of these stocks to go to a company’s Google Finance page, which shows the company’s financials, related news, and allows you to compare it with other commodities.

While there are other, more robust tools you can use to track securities, Google Finance is the only one that can effectively integrate with Google Sheets. Whether you’re a novice to stocks or an experienced trader, this integration is the easiest way to import and monitor stock data in a spreadsheet.

By the way, Google Finance’s sheets integration is only available in English and doesn’t include most international exchanges yet. So if you want to transact on Asian or European exchanges, this may not be the best option for you.

The Google Finance Function

The function that pulls stock data is called “GOOGLEFINANCE.” The syntax of the function is quite simple and uses five arguments, four of which are optional.

The first argument is the ticker symbol. These are codes that companies have when they’re listed on the stock market, such as GOOG for Google or BAC for Bank of America. You can also specify the stock exchange that your chosen stock is listed in to avoid discrepancies. Since Bank of America is listed on the New York Stock Exchange, you’d type “NYSE:BAC.”

To get the ticker codes and exchanges of you’re desired stocks, you’ll have to do some research. You can look for them on Google Finance or your chosen portfolio management tool.

The second argument is the attribute you’d like to display. By default, it’s set to “price” if you leave it blank. Here are a few of the attributes you can pull out using the function:

• price: The price of the specific stock in real-time.
• volume: The current trading volume.
• high: The current or chosen day’s high price.
• low: The current or chosen day’s low price.
• volumeavg: The average trading daily trading volume.
• pe: The price to earnings ratio.
• eps: The earnings per share.

Take note that the attributes you can display vary on whether you’re using current or historical data. Here’s a full list of attributes that you can use for the argument. It’s important to remember that current data refreshes every 15 minutes, so it’s not completely real-time.

The third argument is the start date, which only applies if you’re using historical data. You can type “TODAY()” or leave it blank to display real-time data. The fourth argument specifies either the end date or the number of days from the start date. If left blank, the function will return the data from a single day.

The last argument is the interval, which allows you to specify the frequency of the data. You can set it to “DAILY” or “WEEKLY.”

One thing to note is that Google Sheets processes the ticker symbol and attribute arguments as text, so make sure to place quotation marks around them, or you will get an error.

Stock Tracking in Action

For this example, let’s say you want to look up the current price of a stock of Facebook. Facebook is listed on NASDAQ with the ticker code FB. In this case, you’ll type down the first argument as “NASDAQ:FB” along with “price” as the attribute. So the formula for this would be =GOOGLEFINANCE(“NASDAQ:FB”,”price”) .

If you want to view the daily closing prices for a particular week, such as the week of October 15, 2018, you will specify that date range in the third and fourth arguments. The code for that becomes =GOOGLEFINANCE(“NASDAQ:FB”,”price”,DATE(2018,10,15),DATE(2018,10,20)) . Take note that viewing historical data expands the generated information into array data, which takes up nearby cells.

You can also use the function to generate data for a list of stocks automatically. Just type out the tickers in a column, then use the cells in your first argument. Since our ticker code is in cell C4, you’d type out =GOOGLEFINANCE(C4,”price”) . Below is a list of stocks with their corresponding current prices.

If you want to track a list of attributes, you can type them out in separate cells like in the above image. Then, you can link the second argument to the cell with the attribute’s name. For the price cell for NYSE:IBM in the example below, the formula would be =GOOGLEFINANCE(C$ 2,$ B5) .

Maximizing Google Sheets

The best part of having your stocks on Google Sheets is that you can use various data manipulation tools on them.

For example, let’s say you want to use Google Sheets to keep track of the value of all of your financial assets, such as your stocks, savings accounts, time deposits, and more. Using Finance, the price of your stocks will update in real-time, so you get a full picture of your position at any given time.

Converting Currency with Sheets

Another great function of Google Sheets is that it can convert currencies in real-time. You can do this by typing the stock ticker “CURRENCY:” followed by the codes of the two currencies you want to convert, such as “USDGBP” or “EURJPY.” You can also display historical currency data by specifying a date.

For example, if you’re living in Europe and you want to convert some USD into the Euro, you’d type =GOOGLEFINANCE(“CURRENCY:USDEUR”) and multiply that number with the amount of USD you’re converting.

This has a lot of great use-cases besides foreign exchange trading. For example, if your line of work involves getting paid in a different currency, you can set up an invoice that automatically converts the payments you receive to your home currency.

By Matthew Guay – Published May 16, 2017 –

Spreadsheets are a great tool to track your store’s inventory, list all of your products and supplies and resources, and log whenever anything’s added or taken out.

The only problem is, it can be tedious to update and edit your spreadsheet every time a new product is added to your stock or a new sale is made that decreases your inventory.

Here’s how to automatically manage your inventory in a Google Sheets spreadsheet with a Google Forms form, and some Zapier workflows.

Manage Your Inventory in a Google Sheets Spreadsheet

We’ll do the easy part first—listing your initial inventory. Just open Google Sheets, make a new spreadsheet, then list your inventory there. Be sure to add a least a column for your product ID numbers—or SKU for stock keeping units—and the quantity of the items you currently have.

Add and Remove Inventory with a Google Sheets Form

Now, we’ll create a new Google Form to update inventory. Open Google Forms, or click Tools -> Create a Form in your spreadsheet.

In the form, add the fields you’d want to update—including at least the product ID or SKU, and the number of stock you’re adding (or removing—which you’ll do by adding a negative quantity). You can also add a validation to your quantity—click the 3 dot menu on that question, select Response Validation, and add the details you need to make sure you have the correct data.

Google Forms will now log every update made to your form to a new sheet in your spreadsheet. All we need to do is use that data to update your inventory spreadsheet.

Update Your Inventory Spreadsheet with Zapier

It’s time for the fun part—tying it all together. We’ll use app automation tool Zapier to take your form answers and merge them with your inventory list in Google Sheets.

Just make a new Zap using our Google Forms integration, and select the New Response in Spreadsheet trigger. Choose the spreadsheet you made before, and the sheet in it with the responses from your form.

Now add another step to your Zap, choosing the Google Sheet app and the Lookup Spreadsheet Row action. We want to find the original inventory that you want to update.

Select the correct spreadsheet and your inventory sheet again, then choose your product ID or SKU column as the Lookup Column. Then, click the + icon beside the Lookup Value field in Zapier, and select the product ID value from your Google Form.

Tip: Want to add new products to your spreadsheet from this form, too? Tick the Create Google Sheets Spreadsheet Row if it doesn’t exist yet? box, then fill in the info from your form to add new products if this item isn’t in your inventory yet.

It’s time to crunch the numbers and see what your new quantity is. Add another step to your Zap, select the Formatter app and the Numbers action, then choose the Perform Math Operation transform. Select the Add operator, then click the + icons under Input to add the quantity values from your original inventory list and the form entry.

Now let’s put it all together with one final step. Select the Google Sheets app, and the Update Spreadsheet Row action. Select your spreadsheet and sheet again, and select Use a Custom Value under the Row field. Now, select the ID from your first Google Sheets step in the Custom Value field

Only one thing left: Click the + icon beside your product quantity fields, and select the Output value from your Formatter step. That’ll add the new quantity to your spreadsheet, to keep your inventory up to date.

Turn on the Zap, and share the form with your team. Now, whenever you need to update inventory in Google Sheets, just fill out that form and your inventory will be updated automatically.

To help you set that up, here are some pre-made Zaps to start with:

Whether you’re managing a growing eCommerce business and need details on your inventory, or just want to track where all of the books from your personal library are, Google Forms plus Google Sheets are a great combo to keep tabs on all of your stuff.

Learn more about Google Sheets and Forms in Zapier’s free eBook, The Ultimate Guide to Google Sheets.

Have another question about Google Sheets, Google Forms, or other apps? Ask the Zapier team

Published May 19, 2017

Question asked by Anthony

Answer written by Zapier writer Matthew Guay

This eBook was crafted for you with love by the Zapier team.

Previous Chapter

How do you See who You’ve Shared Folders With in Dropbox?

Next Chapter

How do you Graph Your Unread Email Count in Gmail?

Build workflows with your apps.

Connect apps. Automate tasks. Get more done.

“If we didn’t get these ongoing notifications through Zapier, we’d miss important information on how patient funding is going. It’s saved us so much time.”

Grace Garey, co-founder of Watsi

Take the Work out of Workflow

Zapier is the easiest way to automate powerful workflows with more than 2,000 apps.

Options Trading from Scratch

Learn everything you need to know about successful options trading with this three-part video course.

Market Data and Calendars

Looking for new stock ideas? Want to see which stocks are moving? View our full suite of financial calendars and market data tables, all for free.

Investing Education and Resources

Receive a free world-class investing education from MarketBeat. Learn about financial terms, types of investments, trading strategies and more.

MarketBeat empowers individual investors to make better trading decisions by providing real-time financial data and objective market analysis. Whether you’re looking for analyst ratings, corporate buybacks, dividends, earnings, economic reports, financials, insider trades, IPOs, SEC filings or stock splits, MarketBeat has the objective information you need to analyze any stock. Learn more.

© American Consumer News, LLC dba MarketBeat® 2010-2021. All rights reserved.
326 E 8th St #105, Sioux Falls, SD 57103 | [email protected] | (844) 978-6257
MarketBeat does not provide financial advice and does not issue recommendations or offers to buy stock or sell any security. Learn more.

© 2021 Market data provided is at least 10-minutes delayed and hosted by Barchart Solutions. Fundamental company data provided by Morningstar and Zacks Investment Research. Information is provided ‘as-is’ and solely for informational purposes, not for trading purposes or advice, and is delayed. To see all exchange delays and terms of use please see disclaimer.

This is the Dec 4th entry for Trading API Advent Calendar. Yesterday was a post with Yoshi’s futuristic view on automation. We still have slots available for this Advent Calendar so please reach out to me (@umitanuki) or @AlpacaHQ if you are down to write something (pretty much anything is ok)!

You might think API trading is only for hard-core HFT programmers. That’s not true! If you have used a spreadsheet app with some formula, you are ready for algo trading. Today, I wanted to show you how I built portfolio management using Google Spreadsheet. Sounds hard? Not really.

Like VBA in Microsoft Excel, Google Spreadsheet comes with a script language called App Script to extend its functionality. Unlike VBA which roots from Bill Gates’ favorite Basic language, Google chose JavaScript as a thought-leader in the web age. While not all modern JS functionality is available, it is pretty handy and well-documented.

App Script is for many Google Apps as well as used as standalone app, but today we use it from Spreadsheet. In order to do so, you create a new spreadsheet, and select [Tools] -> [Script editor] from the menu.

It opens a editor in browser and you can start writing App Script. Write a simple function like

Then you can call this function in the cell formula like

Alpaca provides RESTful web API for free. It allows you to get account, positions, and orders information as well as submit orders through the simple HTTP interface. It’s so simple that you can imagine you can call it from JavaScript. All you need is a HTTP request. App Script actually comes with a built-in class called UrlFetchApp that is very flexible to send any type of HTTP request.

If you have ever interacted with any stock trading app, you may find something is missing. Or too much. It’s never perfect because every single stock trader has her own needs. If you are managing dozens of small positions in your portfolio, it’s not even easy to see all the positions quickly. Or you just want to calculate some metrics using sum() or average(). Or you want to customize your view with your favorite fields. If existing apps don’t work for you, why don’t you build your own?

I actually share my sample spreadsheet with working script (also the full code is attached below). As this screenshot shows, you can update your portfolio values in one click and see it in a tabular format friendly to you. It can show your account information such as portfolio value and list of positions you hold. Here is the detail step you can follow to build your own.