forcode：很多人可能都没有尝试过google docs spreadsheet 强大的函数功能，尤其是google finance函数太强大了！你不仅可以在google docs spreadsheet 中查看股票、外汇、黄金等实时行情，还可以根据你的金融知识自定义各种公式来计算各种指标帮助你进行投资决策。比如，你可以把黄金、外汇、股票的价格、成交量、相对于基期的波动幅度等呈现在一个页面，并且可以按照你喜欢的风格来修饰这个表格，更重要的是，你还可以将这个表格发布成一个页面，与朋友分享，太棒了。

        当我们需要以历史上某一个特殊日期（比如2007年10月16日上证指数最高点）作为基期计算目前股票价格相对于基期的指数时，就需要一个函数来获得该值，但是，google docs spreadsheet 本身并没有函数可以在“一个单元格”中获取任何一支股票在历史上任何一天的收盘价。

        GoogleFinance()函数用来获取股票历史行情的表达式：
        =GoogleFinance("symbol", "attribute", "start_date", "num_days|end_date", "interval")
        获得的是一个2×2（或N×2）的表格，如下图所示：

        经过forcode半个小时的钻研，终于编写出在google docs spreadsheet中获取历史上任何一支股票任何一天股票收盘价的函数表达式了，如下所示：
        =value(mid(concatenate(GoogleFinance(B2,"price",$M$1,$M$1)),29,8)) 
        其中$M$1是日期所在的单元格,B2是股票代码所在的单元格，该函数仅对沪市和深市有效，其他股市可能需要修改29为其他数字。

        下面forcode一步一步来解释是如何构造这个函数表达式的。

        首先，通过这个表达式：
        =GoogleFinance(B2,"price",$M$1,$M$1)
        可以获得B2单元格中股票代码所对应股票在$M$1单元格中的日期的价格，但是googlefinance()函数获得的结果是一个2×2的表格，而不是一个单元格，如下图所示：
       

        GoogleFinance()函数的表达式是这样的：
   =GoogleFinance("symbol"; "attribute")
        其中symbol是股票代码，也可以指向一个含有股票代码的单元格，例如A2；attribute是股票参数，默认的是price，即股票价格。常用参数如下：

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

        更多GoogleFinance()函数的资料请参考google docs的帮助文档中的相关内容。

        为了提取我们所需要的历史上任何一天某股票的收盘价，我们需要先使用concatenate()函数将这2×2的四个单元格的内容合并到一个单元格。

        concatenate()函数的表达式如下：
        CONCATENATE(text_1, text_2, ..., text_30)   
        concatenate()函数的作用是：Combines several text strings into one string. Text_1, text_2, ... text_30 are text passages that are to be combined into one string.

        通过这个表达式：
        =concatenate(GoogleFinance(B2,"price",$M$1,$M$1))
        可以看到合并之后的字符串的样子：
        DateClose10/16/2007 15:00:0019358.441
        美国股市的股票合并之后的字符串略有不同，因为收盘时间不一样，如下图所示：
       

        然后，我们需要从这一长串字符串中截取我们需要的股票价格，即“DateClose10/16/2007 15:00:0019358.441”中的19358.441，我们可以使用mid()函数，这个函数其实也是openoffice中的函数。

        mid函数的表达式为：MID(text, start, number)   
        mid函数的作用是：Returns a text segment of a character string. The parameters specify the starting position and the number of characters. Text is the text containing the characters to extract. Start is the position of the first character in the text to extract. Number is the number of characters in the part of the text.

        通过这个表达式：
        =mid(concatenate(GoogleFinance(B2,"price",$M$1,$M$1)),29,8)
        可以截取我们所需要的股票价格，但此时获得的仍然是字符串格式的股票价格，我们还需要使用value()函数将它转化为数字格式，表达式如下：
        =value(mid(concatenate(GoogleFinance(B2,"price",$M$1,$M$1)),29,8))

        forcode在琢磨这个表达式的过程中，受到了国外网站上“Tracking your stock portfolio using Google Docs”这篇文章的启发，才知道google docs的spreadsheet支持函数表达式嵌套，才会去尝试各种表达式组合，在此表示感谢。但是这篇文章中提到的方法：
        First in cell M2 we enter
        =concatenate(GoogleFinance(B2,"price",C2))
        and then in the cell where the historical price is needed we enter
        =value(mid(M2,3+find(":",M2,1+find(":",M2)),99))

        经forcode测试没有成功，而且，这个方法有一个很大的缺点：它由两个表达式组成，需要用另外一个表格来计算中间结果，不是直接用一个公式得到任何一支股票在历史上某一天价格，可移植性大大减弱。

热议文章

• 2008-08-04 -- 发生在我身上一件非常不可思仪的事情，说出来只想发泄一下，太恐（转帖） (164)
• 2009-05-06 -- forcode奇想：超越支付宝的openmoney (26)
• 2007-12-13 -- 马桶 (22)
• 2008-05-23 -- forcode奇想：从白人狐臭看不同人种间的生殖壁垒 (20)
• 2006-02-21 -- 大陆身份证生成器.exe (19)
• 2008-05-13 -- 四川汶川地震最新现场图片惨不忍睹（组图） (19)
• 2008-10-30 -- 可以缩短软件版权保护期吗？ (15)
• 2008-07-31 -- 看完两个极度恶心的视频后，我确信李银河老师错了（视频） (13)
• 2007-09-06 -- 调查 (12)
• 2008-07-06 -- （转）日本A-片製作流程(附图) (11)

written by forcode