How to Track Dow Stocks in Google Sheets via Google Finance

The Dow Jones Industrial Average is an important barometer of the U.S. stock market. Its 30 constituents, such as Apple Inc.AAPL) The Boeing Company (BA ), JPMorgan Chase (JPM ) and Walmart (WMT )) epitomizes the major sectors of the U.S. economy.

Now you can use Google Finance, imported into Google Sheets, to track one of the most popular stock indices in the U.S. The dynamic spreadsheet created by TheStreet is updated throughout the trading day and allows you to track basic data such as price, percentage change and market capitalization.

Related contentHow do I protect my data in Google Sheets? Preventing accidental changes

Below is an electronic spreadsheet that we have designed for you to track the Dow components throughout the trading day. You can sort each stock by name, percentage change and market capitalization. You can also download the spreadsheet and customize it yourself.

A spreadsheet version of this tutorial can be downloaded here. Copy a copy of the worksheet by selecting Copy from the drop-down menu of the File menu.

How to use this tracker

Google Finance allows you to monitor specific stocks by selecting specific attributes such as Latest Price, Rock High and Low and Daily Price Change. In this spreadsheet, we have listed 20 available attributes in a drop-down list in the cell for real-time monitoring.

The first column on the left (column A) lists the stock codes of the Dow stocks, and the seven consecutive columns on the right (columns B through I) default to seven attributes: "name" (company name), "price" (latest price), "change" (dollar value change), "changepct" (percentage change), "high" (rock-high price), and "low" (low price). change" (dollar value change), "changepct" (percentage change), "high" (highest price in Panaka), "low" (lowest price in Panaka), "high" (highest price in Panaka), "low" (lowest price in Panaka), "high" (highest price in Panaka), and "low" (lowest price in Panaka). high", "low", "tradetime", and "marketcap" (market capitalization in U.S. dollars).

Each of these seven columns has a drop-down list of cells that you can change to any of the other 20 attributes.

In order to help quantifiable data, we have applied conditional formatting to some attributes, namely "change", "changepct" and "market cap". For "change" and "changepct", an increase in the daily change in price will show the cell in green and a decrease will show the cell in red. For no change, the cell remains white.

For "marketcap," the market capitalization of the 30 Dow stocks shows a gradient from dark yellow for the largest market cap companies at the top of the list to bright yellow for the smallest market cap companies at the bottom.

In order to maintain the integrity of the macros used in the spreadsheet, "changepct" and "marketcap" (columns E and I) are protected, as changing the attributes of these two columns may lead to different results when running the macro.

Keyboard shortcuts "Command + Option + Shift + 2" and "Command + Option + Shift + 3" sort Dow stocks by percentage change.

View 3 images from this gallery in the original article

Operation Macro

You can manipulate macros using the drop-down menu or the Keystone shortcut. Under Expand in the Spreadsheet menu bar, go to Macros and a list of macros will appear at the bottom of the drop-down menu.

The names of the macros are listed on the left, and their keystone shortcuts (which keys to use) are listed on the right. The macros in this spreadsheet are mainly used to sort data by column.

You can also use the Keystone shortcuts. On the Mac OS, the Keyboard shortcuts are as follows:

After running the macro, 3M is at the top, Walt Disney Company is at the bottom, and all data from A2 through I31 move accordingly in the data sort.

The second macro sorts the data by percentage change from highest to lowest. In this function, the stocks with the largest increases are at the top, and the stocks with the smallest increases or decreases are at the bottom.

The third macro is the opposite of the second macro, sorted from lowest to highest by percentage change. (Sorting by Percentage Change instead of Price Change is justified because the Percentage Change shows how the stock price has changed relative to the previous closing price).

The fourth macro is sorted by market capitalization, with the company with the largest market capitalization at the top of the noodle.

After you get used to running macros, you can create a fifth macro to sort the Dow stocks by market capitalization from smallest to largest. Or create a macro to customize how the data is displayed.

Avoid making changes to the spreadsheet, as any changes may affect the results of the macro. The macro runs based on the cell range of the spreadsheet from A2 to I31 (headings are not affected unless the attributes in the drop-down list are changed).

However, you can change the attributes of certain columns, such as "Price", "Change", "Maximum", "Minimum " and "Transaction Time". If you do want to customize the spreadsheet by adding columns with different attributes, consider editing the macro to accommodate cell range changes.

Note that Google Finance has a disclaimer at the bottom of the spreadsheet informing users of the limitations and usage of the stock data: "Quotes are not available from all markets and may be delayed by up to 20 minutes. Information is provided 'as is' for informational purposes only and is not to be used for trading purposes or advice".

Therefore, trading on the basis of such information is at your own risk.

Again, we have done all the work for you. A spreadsheet version of this tutorial can be downloaded here. You can easily copy a worksheet by selecting "Copy" from the drop-down menu of the "File" menu.

Watch Dow Tracker Tutorials

This is just one of many articles about the easy-to-use tools in Google Sheets. Please read some of the highlights of the noodles below and let us know what you would like to see in the next article. Email us at [email protected]

Related Articles How to Track Stocks from Google Finance in Google Sheets

Related posts to how to create a stock tracker with real-time data using Google Finance

Related: How to create a dropdown menu in a cell on Google Sheets

Related:How to Create a Dropdown List in a Cell on Google SheetsHow to Use Conditional Formatting in Google Sheets: Ranges, Formulas, and More