英文:
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个查询。
我在想是否有更好的方法。理想情况下，我想要：
- 仅针对每次10个股票调用
googlefinance() - 每5分钟进行一次此调用。例如，1PM时获取前10个股票数据，1:05PM获取接下来的10个，以此类推。
- 在额外的列
K中记录取得数据的日期。 - 我只想每天获取数据一次。我对盘中变化不感兴趣。
- 所以一些脚本会按顺序处理列表，每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:
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:
- Call on
googlefinance()for only, say, 10 tickers at a time - 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.
- In an extra column
KI would note the date when for that ticker the data was retrieved - I would only want to retrieve data once per day. I am not interested in intraday changes
- 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);
}
}
编辑：
- 那是我的错误，在下面已经修复。
- 您可以在下面编辑cellsToUpdateNum变量
- 是的，我也记得您只想要获取今天的价格，已包含该功能。
- 是的，它有效。
//@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'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'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, <br>
- [Official doc][1]
- [A tutorial][2]
<br>
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("C1");
var index = indexCell.getValue();
// calculate range
var startRow = index;
var endRow = startRow + 9;
// get cells
var tickerRange = sheet.getRange("A" + startRow + ":A" + endRow);
var tickerValues = tickerRange.getValues();
var formulaRange = sheet.getRange("B" + startRow + ":B" + endRow);
// set formulas
for (var i = 1; i < tickerValues.length; i++) {
var ticker = tickerValues[i][0];
var formula = "=GOOGLEFINANCE(\"" + ticker + "\")";
formulaRange.getCell(i, 1).setFormula(formula);
}
// update index
indexCell.setValue(startRow + 10);
// if reached end, reset index
if (startRow + 10 > 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'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("index");
// if doesn't exist in properties, initiate
if (index === null) {
index = 1;
}
// convert to int from floating point because we can'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("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();
// get today's date
var timezoneString = "GMT+3"; //you can format timezone as you like
var dateFormatString = "dd/MM/yyyy"; //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 < tickerValues.length; i++) {
var dateCellValue = dateValues[i][0];
// reminder that google sheets localizes date in the sheets with your locale, if your locale doesn't match dateFormatString errors will probably occur.
dateCellValue = Utilities.formatDate(new Date(dateCellValue), timezoneString, dateFormatString);
var ticker = tickerValues[i][0];
if (ticker!="") { //to not write to empty rows
if (today!=dateCellValue) { //don't update and use quota if we updated the prices today
var formula = "=GOOGLEFINANCE(\"" + ticker + "\")";
formulaRange.getCell(i+1, 1).setFormula(formula); //update ticker prices
dateRange.getCell(i+1,1).setValue(today); //update date
}
}
}
// update index
PropertiesService.getUserProperties().setProperty("index", index+cellsToUpdateNum);
// if reached end, reset index
if (startRow + cellsToUpdateNum > sheet.getLastRow()) {
PropertiesService.getUserProperties().setProperty("index", 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>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论