谷歌表格_如何使用Google表格跟踪库存

谷歌表格

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.

Google表格中鲜为人知的功能之一是GOOGLEFINANCE，可让您跟踪股市上的当前或历史金融证券数据。这是使用方法。

什么是Google财经？ (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.

财务是Google的实时工具，可显示当前市场信息并汇总商业新闻。它当前已与Google搜索集成，因此，如果您在Google上查找特定公司的股票代码，例如Walmart的WMT或Apple的AAPL，您将立即看到该证券的当前股票报价和历史数据。您可以单击其中一种股票进入公司的Google财经页面，该页面显示该公司的财务，相关新闻，并允许您将其与其他商品进行比较。

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.

虽然您可以使用其他更强大的工具来跟踪证券，但Google财经是唯一可以与Google表格有效集成的工具。无论您是股票的新手还是经验丰富的交易者，这种集成都是在电子表格中导入和监视股票数据的最简单方法。

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.

顺便说一句，Google财务的工作表集成仅提供英语版本，尚不包括大多数国际交易所。因此，如果您想在亚洲或欧洲交易所进行交易，这可能不是您的最佳选择。

Google财务功能 (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.

提取库存数据的功能称为“ GOOGLEFINANCE”。该函数的语法非常简单，并使用五个参数，其中四个是可选的。

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.”

第一个参数是股票代号。这些是公司在股票市场上上市时所具有的代码，例如Google的GOOG或美国银行的BAC。您还可以指定列出所选股票的证券交易所，以免出现差异。由于美国银行已在纽约证券交易所上市，因此您需要输入“ 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.

要获取股票代码和所需股票的交易所，您必须进行一些研究。您可以在Google财经或所选的投资组合管理工具中查找它们。

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.

volumeavg：平均每日交易量。
pe: The price to earnings ratio.

pe：市盈率。
eps: The earnings per share.

eps：每股收益。

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.

请注意，根据您使用的是当前数据还是历史数据，可以显示的属性会有所不同。这是可用于参数的属性的完整列表。重要的是要记住，当前数据每15分钟刷新一次，因此它不是完全实时的。

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.

第三个参数是开始日期，仅在使用历史数据时才适用。您可以键入“ TODAY()”或将其保留为空白以显示实时数据。第四个参数指定结束日期或距离开始日期的天数。如果留空，该函数将返回一天的数据。

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.

需要注意的一件事是Google表格将股票代码和属性参数作为文本处理，因此请确保在引号和属性参数周围加上引号，否则会出现错误。

实际库存跟踪 (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").

对于此示例，假设您要查找Facebook股票的当前价格。 Facebook在纳斯达克上市，股票代码为FB。在这种情况下，您将第一个参数键入为“ NASDAQ：FB”，并将“ price”作为属性。因此，此公式将为=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.

如果要查看特定一周(例如，2018年10月15日那一周)的每日收盘价，请在第三个和第四个参数中指定该日期范围。该代码变为=GOOGLEFINANCE("NASDAQ:FB","price",DATE(2018,10,15),DATE(2018,10,20)) 。请注意，查看历史数据会将生成的信息扩展为数组数据，该数据会占用附近的单元格。

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.

您还可以使用该功能自动生成股票清单的数据。只需在一栏中输入代码，然后在第一个参数中使用单元格即可。由于我们的代码在单元格C4中，因此您需要输入=GOOGLEFINANCE(C4,"price") 。以下是具有相应当前价格的股票列表。

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).

如果要跟踪属性列表，可以像上图一样在单独的单元格中键入它们。然后，您可以将第二个参数链接到具有属性名称的单元格。在下面的示例中，对于NYSE：IBM的价格单元格，公式将为=GOOGLEFINANCE(C$2,$B5) 。

最大化Google表格 (Maximizing Google Sheets)

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

将股票存储在Google表格中的最好的部分就是可以使用各种数据处理工具。

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.

例如，假设您要使用Google表格来跟踪所有金融资产的价值，例如股票，储蓄账户，定期存款等。使用财务，您的股票价格将实时更新，因此您可以在任何给定时间获得自己头寸的全貌。

用工作表转换货币 (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.

Google表格的另一个强大功能是可以实时转换货币。为此，您可以输入股票行情代码“ CURRENCY：”，然后输入要转换的两种货币的代码，例如“ USDGBP”或“ EURJPY”。您还可以通过指定日期来显示历史货币数据。

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.

例如，如果您居住在欧洲，并且想要将一些美元转换为欧元，则可以输入=GOOGLEFINANCE("CURRENCY:USDEUR")并将该数字乘以您要转换的美元金额。

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.

除了外汇交易，这还有很多很好的用例。例如，如果您的工作范围涉及用其他货币付款，则可以设置发票，该发票将自动将您收到的付款转换为您的本币。

翻译自: https://www.howtogeek.com/449743/how-to-track-stocks-with-google-sheets/