调用脚本中的googlefinance,并且一次只对少量股票运行它。

huangapple go评论60阅读模式
英文:

Invoke googlefinance from a script and only run it for a small batch of tickers at a time

问题

我有一个Google表格,其中我正在收集一些与股票相关的数据。我使用googlefinance()函数,以及其他一些函数:

// 当前价格
=if($D$1=true,googlefinance(_ticker(A3),"price"),D3)

// 5年最低价
=if($D$1=true, min(index(googlefinance(_ticker(A3), "price", date(year(today()) - 5, month(today()), day(today())), today()), 0, 2)),E3)

// 5年趋势
=if($D$1=true, sparkline(googlefinance(_ticker(A3), "price", today()-1825, today(), "weekly"), {"charttype","line";"linewidth",1;"color","#5f88cc"}), J3)

由于清单中有1000多个股票代码,我在D1中使用复选框来有意激活使用googlefinance()的公式,以避免被限制或出现太多Internal Error: xx retuned no result错误,这些错误googlefinance()偶尔会随机生成。

然而,这还不够。当我检查D1时,我仍然会收到很多错误,因为该表正在发出大约1000(股票)x 5(使用googlefinance()的列)= 5000个查询。

我在想是否有更好的方法。理想情况下,我想要:

  1. 仅针对每次10个股票调用googlefinance()
  2. 每5分钟进行一次此调用。例如,1PM时获取前10个股票数据,1:05PM获取接下来的10个,以此类推。
  3. 在额外的列K中记录取得数据的日期。
  4. 我只想每天获取数据一次。我对盘中变化不感兴趣。
  5. 所以一些脚本会按顺序处理列表,每5分钟填充下一组10个股票。一旦完成,它就会从头开始,但如果当前日期等于列K中的“上次检索”的日期,则不执行任何操作。

问题是我不知道如何做到这一点。在测试中,我尝试从Google表格底层的Google Apps脚本中调用googlefinance(),但只能直接从单元格内调用该函数。

我现在没有任何想法。有人知道如何做到这一点吗?

示例表格:https://docs.google.com/spreadsheets/d/1_Tl0LK2hvc3GzBLXUtomI-cAuFdOQCj7HxfdnkrStJg/edit#gid=0

英文:

I have a Google Sheet in which I'm gathering some stock-related data. I'm using the googlefinance() function, among others:

调用脚本中的googlefinance,并且一次只对少量股票运行它。

Some formula's I use:

// current price
=if($D$1=true,googlefinance(_ticker(A3),"price"),D3)

// 5 year low
=if($D$1=true, min(index(googlefinance(_ticker(A3), "price", date(year(today()) - 5, month(today()), day(today())), today()), 0, 2)),E3)

// 5 year trend
=if($D$1=true, sparkline(googlefinance(_ticker(A3), "price", today()-1825, today(), "weekly"), {"charttype","line";"linewidth",1;"color","#5f88cc"}), J3)

Since the list is over 1'000 tickers, I used the checkbox in D1 to purposefully activate the formula's using googlefinance() to not get rate limited or have too many Internal Error: xx retuned no result-errors that googlefinance() just happend to randomly generate.

This measure is not enough though. I still get many errors when I'm checking D1, because the sheet is firing somewhat of 1'000 (tickers) x 5 (columns using googlefinance()) = 5'000 queries.

I was wondering if there is no better way. Ideally, I would:

  1. Call on googlefinance() for only, say, 10 tickers at a time
  2. This call would be, for instance, every 5 minutes. So the first 10 tickers at 1PM, the second 10 at 1:05PM, the next 1:10PM, etc.
  3. In an extra column K I would note the date when for that ticker the data was retrieved
  4. I would only want to retrieve data once per day. I am not interested in intraday changes
  5. So some script would work its way down the list, filling the next 10 tickers every 5 minutes. Once it's done, it starts from the top, but if the current date equals the "last retrieved" date from column K, nothing is done.

The problem is that I have no clue on how to do this. In a test, I tried invoking googlefinance() from a Google Apps script underlaying the sheet, but you can only invoke the function from within a cell directly.

I am without ideas not. Does someone know how to do this?

Sample sheet: https://docs.google.com/spreadsheets/d/1_Tl0LK2hvc3GzBLXUtomI-cAuFdOQCj7HxfdnkrStJg/edit#gid=0

答案1

得分: 0

Here's the code you provided translated into Chinese:

很不幸您无法在应用脚本内调用表格功能您必须从Web API获取数据或将公式设置到列中
然而Google的财务API不再有效唯一的选择是抓取这个网站 https://www.google.com/finance/quote,这可能有些困难。不过,您也可以使用另一个API,如Alpha Vantage。

我将在后一种选项中提供一个示例
为了抵消速率限制正如您所说我们将只更新十个单元格为此我们将将一个索引存储到一个单元格中我选择了C1请不要忘记手动输入一个初始值我假设它被设置为1当最后一个单元格被更新时它将从1开始

对于时钟设置一个基于时间的触发器您可以选择要运行的函数以及运行的频率这里有一些有用的资源

- [官方文档][1]
- [教程][2]

您可以根据自己的喜好自定义代码希望我的答案对您有用

```javascript
function updateTickerData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 读取索引
  var indexCell = sheet.getRange("C1");
  var index = indexCell.getValue();
  
  // 计算范围
  var startRow = index;
  var endRow = startRow + 9; 
  
  // 获取单元格
  var tickerRange = sheet.getRange("A" + startRow + ":A" + endRow);
  var tickerValues = tickerRange.getValues();
  var formulaRange = sheet.getRange("B" + startRow + ":B" + endRow);
  
  // 设置公式
  for (var i = 1; i < tickerValues.length; i++) {
    var ticker = tickerValues[i][0];
    var formula = "=GOOGLEFINANCE(\"" + ticker + "\")";
    formulaRange.getCell(i, 1).setFormula(formula);
  }
  
  // 更新索引
  indexCell.setValue(startRow + 10);
  
  // 如果达到末尾,重置索引
  if (startRow + 10 > sheet.getLastRow()) {
    indexCell.setValue(1); 
  }
}

编辑:

  1. 那是我的错误,在下面已经修复。
  2. 您可以在下面编辑cellsToUpdateNum变量
  3. 是的,我也记得您只想要获取今天的价格,已包含该功能。
  4. 是的,它有效。
//@OnlyCurrentDoc
function upgradeTickerPrices() {

  // 获取工作表
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // 读取索引
  var index=null;
  index = PropertiesService.getUserProperties().getProperty("index");
  // 如果在属性中不存在,初始化
  if (index === null) {
    index = 1;
  }
  // 转换为整数,因为我们不能有浮点数,例如5.0个单元格
  index = index | 0;

  // 设置每次运行要更新的单元格数量
  var cellsToUpdateNum = 15; // 您可以在这里自定义数量

  // 计算单元格范围
  var startRow = index;
  var endRow = startRow + cellsToUpdateNum - 1; 

  // 获取单元格
  // A列是股票名称,B列是值,C列是股票更新日期
  var tickerRange = sheet.getRange("A" + startRow + ":A" + endRow);
  var tickerValues = tickerRange.getValues();
  var formulaRange = sheet.getRange("B" + startRow + ":B" + endRow);
  var dateRange = sheet.getRange("C" + startRow + ":C" + endRow);
  var dateValues = dateRange.getValues();

  // 获取今天的日期
  var timezoneString = "GMT+3"; // 您可以按照自己的喜好格式化时区
  var dateFormatString = "dd/MM/yyyy"; // 您可以按照自己的喜好格式化日期
  var today = Utilities.formatDate(new Date(), timezoneString, dateFormatString); 

  // 如果条件匹配,更新股票价格
  for (var i = 0; i < tickerValues.length; i++) {
    var dateCellValue = dateValues[i][0];
    // 提醒您,Google表格会使用您的区域设置本地化日期,如果您的区域设置与dateFormatString不匹配,可能会发生错误。
    dateCellValue = Utilities.formatDate(new Date(dateCellValue), timezoneString, dateFormatString);

    var ticker = tickerValues[i][0];

    if (ticker!="") { // 不要写入空行
      if (today!=dateCellValue) { // 不要更新并使用配额如果我们今天已经更新了价格
        var formula = "=GOOGLEFINANCE(\"" + ticker + "\")";
        formulaRange.getCell(i+1, 1).setFormula(formula); // 更新股票价格
        dateRange.getCell(i+1,1).setValue(today); // 更新日期
      }
    }

  }

  // 更新索引
  PropertiesService.getUserProperties().setProperty("index", index+cellsToUpdateNum);

  // 如果达到末尾,重置索引
  if (startRow + cellsToUpdateNum > sheet.getLastRow()) {
    PropertiesService.getUserProperties().setProperty("index", 1);
  }
}

请注意,这些翻译仅为代码部分,不包括评论和文档链接。
<details>
<summary>英文:</summary>
Unfortunately, you can not call sheets function inside apps script. You either have to get data from a web API or set formulas to the columns.
However, Google&#39;s finance API no longer works. The only option is to scrap this site, https://www.google.com/finance/quote, which is difficult. Though you can also use another API like Alpha Vantage.
I will provide an example in the latter option.
To counteract rate limitation, as you said, we will update only ten cells. To do that, we will store an index to a cell, I chose C1, don&#39;t forget to enter an initial value manually. I assume it is set to 1. When the last cell is updated, it starts from 1 again.
For the clock, set a time based trigger. You can choose the function to run and how frequent it runs. Here are some helpful resources, &lt;br&gt;
- [Official doc][1] 
- [A tutorial][2]
&lt;br&gt;
You can customize the code as you like. I hope my answer is useful.
function updateTickerData() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// read index
var indexCell = sheet.getRange(&quot;C1&quot;);
var index = indexCell.getValue();
// calculate range
var startRow = index;
var endRow = startRow + 9; 
// get cells
var tickerRange = sheet.getRange(&quot;A&quot; + startRow + &quot;:A&quot; + endRow);
var tickerValues = tickerRange.getValues();
var formulaRange = sheet.getRange(&quot;B&quot; + startRow + &quot;:B&quot; + endRow);
// set formulas
for (var i = 1; i &lt; tickerValues.length; i++) {
var ticker = tickerValues[i][0];
var formula = &quot;=GOOGLEFINANCE(\&quot;&quot; + ticker + &quot;\&quot;)&quot;;
formulaRange.getCell(i, 1).setFormula(formula);
}
// update index
indexCell.setValue(startRow + 10);
// if reached end, reset index
if (startRow + 10 &gt; sheet.getLastRow()) {
indexCell.setValue(1); 
}
}
Edit:
1. That was my error, fixed below.
2. You can edit the cellsToUpdateNum variable below
3. Yes, I also remembered that you only want to get today&#39;s prices, included that feature.
4. Yes, it works.
Code:
//@OnlyCurrentDoc
function upgradeTickerPrices() {
// get sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// read index
var index=null;
index = PropertiesService.getUserProperties().getProperty(&quot;index&quot;);
// if doesn&#39;t exist in properties, initiate
if (index === null) {
index = 1;
}
// convert to int from floating point because we can&#39;t have, for example, 5.0 cells
index = index | 0;
// set number of cells to update in each run
var cellsToUpdateNum = 15; // you can customize the number here
// calculate range of cells
var startRow = index;
var endRow = startRow + cellsToUpdateNum - 1; 
// get cells
// A is ticker names, B is values, C is ticker update date
var tickerRange = sheet.getRange(&quot;A&quot; + startRow + &quot;:A&quot; + endRow);
var tickerValues = tickerRange.getValues();
var formulaRange = sheet.getRange(&quot;B&quot; + startRow + &quot;:B&quot; + endRow);
var dateRange = sheet.getRange(&quot;C&quot; + startRow + &quot;:C&quot; + endRow);
var dateValues = dateRange.getValues();
// get today&#39;s date
var timezoneString = &quot;GMT+3&quot;; //you can format timezone as you like
var dateFormatString = &quot;dd/MM/yyyy&quot;; //you can format date as you like
var today = Utilities.formatDate(new Date(), timezoneString, dateFormatString); 
// update ticker prices if conditions match
for (var i = 0; i &lt; tickerValues.length; i++) {
var dateCellValue = dateValues[i][0];
// reminder that google sheets localizes date in the sheets with your locale, if your locale doesn&#39;t match dateFormatString errors will probably occur.
dateCellValue = Utilities.formatDate(new Date(dateCellValue), timezoneString, dateFormatString);
var ticker = tickerValues[i][0];
if (ticker!=&quot;&quot;) { //to not write to empty rows 
if (today!=dateCellValue) { //don&#39;t update and use quota if we updated the prices today
var formula = &quot;=GOOGLEFINANCE(\&quot;&quot; + ticker + &quot;\&quot;)&quot;;
formulaRange.getCell(i+1, 1).setFormula(formula); //update ticker prices
dateRange.getCell(i+1,1).setValue(today); //update date
}
}
}
// update index
PropertiesService.getUserProperties().setProperty(&quot;index&quot;, index+cellsToUpdateNum);
// if reached end, reset index
if (startRow + cellsToUpdateNum &gt; sheet.getLastRow()) {
PropertiesService.getUserProperties().setProperty(&quot;index&quot;, 1);
}
}
[1]: https://developers.google.com/apps-script/guides/triggers/installable#time-driven_triggers
[2]: https://yagisanatode.com/google-apps-scripts-create-time-triggers-to-automatically-send-email-task-reminders-from-a-google-sheets-check-list/
</details>
# 答案2
**得分**: -1
我以回答的形式发布,因为我无法在您的问题上发表评论。
在Python和R中,我创建一个包含我想要信息的所有公司的列表,并使用while循环。当列表包含元素时:下载一小批数据,如果下载成功,则从列表中删除这些公司。如果发生错误,请忽略,等待10秒,然后重试。当列表为空时,停止。
很抱歉,我无法为您提供更多帮助,因为我尚未与Google Finance合作过。
<details>
<summary>英文:</summary>
I am posting as an answer because I cannot comment on your question.
In Python and R, I create a list with all companies I want information about and use a while loop. While the list contains elements: download a little batch and, if download is succesful, remove those companies from list. If error occurs, ignore, wait 10sec, and try again. When the list is empty, stop.
I am sorry to not be able to help you more as I have not worked yet with google finance.
</details>

huangapple
  • 本文由 发表于 2023年5月22日 03:07:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/76301488.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定