Reads each symbol on your watchlist from a market-data endpoint you choose and appends the figures to a sheet as a dated row.
Uses
Gmail
Google Sheets
OpenAI
Log watchlist market figures on a schedule
Run every weekday at 17:30.Run every weekday at 17:30.
Set watchlist to blank.Set watchlist to blank.Set quoteUrl to blank.Set quoteUrl to blank.Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.Set sheetName to Market data log.Set sheetName to Market data log.Set emailReportTo to blank.Set emailReportTo to blank.Set maxResponseCharacters to 4000.Set maxResponseCharacters to 4000.
Get the current date and time and save it as recordedAt.Get the current date and time and save it as recordedAt.Get today's date and save it as runDate.Get today's date and save it as runDate.Set figureLines to an empty list.Set figureLines to an empty list.Set skippedLines to an empty list.Set skippedLines to an empty list.Set tabCreated to false.Set tabCreated to false.
If a step below fails, save the error as tabErrorIf a step below fails, save the error as tabErrorTry
Google SheetsCreate the tab sheetName in spreadsheetUrl.Create the tab sheetName in spreadsheetUrl.
Change tabCreated to true.Change tabCreated to true.On error
Show in the output: Adding rows to the existing '@sheetName' tab.Show in the output: Adding rows to the existing '@sheetName' tab.
If tabCreated is yesIf tabCreated is yesYes
Google SheetsAdd a row to sheetName in spreadsheetUrl.Add a row to sheetName in spreadsheetUrl.No
No steps
Split watchlist by , and save it as symbols.Split watchlist by , and save it as symbols.
For each rawSymbol in symbols:For each rawSymbol in symbols:
Make rawSymbol uppercase and save it as symbol.Make rawSymbol uppercase and save it as symbol.If symbol isn't emptyIf symbol isn't emptyYes
Replace SYMBOL with symbol in quoteUrl and save it as requestUrl.Replace SYMBOL with symbol in quoteUrl and save it as requestUrl.Set quote to nothing.Set quote to nothing.Set problem to blank.Set problem to blank.If a step below fails, save the error as quoteErrorIf a step below fails, save the error as quoteErrorTry
Fetch requestUrl and save it as quote.Fetch requestUrl and save it as quote.On error
Change problem to the data source could not be reached (quoteError.message with requestUrl replaced by the endpoint address).Change problem to the data source could not be reached (quoteError.message with requestUrl replaced by the endpoint address).If problem is emptyIf problem is emptyYes
If quote.ok isn't yesIf quote.ok isn't yesYes
Change problem to the data source answered HTTP quote.status as text.Change problem to the data source answered HTTP quote.status as text.No
No steps
No
No steps
Set answerText to blank.Set answerText to blank.If problem is emptyIf problem is emptyYes
Set prompt to You are transcribing figures out of one market-data response. Report only what the response below actually contains.Set prompt to You are transcribing figures out of one market-data response. Report only what the response below actually contains.
Do not estimate, forecast, convert, annualise or round any figure. Do not add a figure the response does not state. Do not say whether the number is high, low, good or bad, do not compare it to anything, and do not give a view on the security or on what anyone should do - this is a record of numbers, not analysis.
Reply with exactly one line and nothing else: four fields separated by the | character, in this order:
price | change | currency | as of
Copy each figure exactly as the response gives it. Use the single word unknown for any of the four the response does not state. Write no other text, no explanation and no units beyond what is already in the figure.
Symbol: symbol
Response from the data source:
quote.body as text truncated to maxResponseCharacters characters.If a step below fails, save the error as readErrorIf a step below fails, save the error as readErrorTry
OpenAIAsk OpenAI: prompt. Save it as reading.Ask OpenAI: prompt. Save it as reading.Trim reading.response_text and save it as answerText.Trim reading.response_text and save it as answerText.On error
Change problem to the figures could not be read out of the response (readError.message).Change problem to the figures could not be read out of the response (readError.message).No
No steps
If problem is empty and answerText is emptyIf problem is empty and answerText is emptyYes
Change problem to the data source response carried no figures to record.Change problem to the data source response carried no figures to record.No
No steps
If problem is emptyIf problem is emptyYes
Split answerText by | and save it as fields.Split answerText by | and save it as fields.Set price to blank.Set price to blank.Set change to blank.Set change to blank.Set currency to blank.Set currency to blank.Set figuresAsOf to blank.Set figuresAsOf to blank.Set rowNote to Recorded figure - not investment advice..Set rowNote to Recorded figure - not investment advice..Set summaryLine to blank.Set summaryLine to blank.If the length of fields is 4If the length of fields is 4Yes
Trim fields[0] and save it as price.Trim fields[0] and save it as price.Trim fields[1] and save it as change.Trim fields[1] and save it as change.Trim fields[2] and save it as currency.Trim fields[2] and save it as currency.Trim fields[3] and save it as figuresAsOf.Trim fields[3] and save it as figuresAsOf.Change summaryLine to symbol: price currency, change change, as of figuresAsOf.Change summaryLine to symbol: price currency, change change, as of figuresAsOf.No
Change rowNote to No figures were taken: the data source reply did not come back as price, change, currency and as-of. Reply was: answerText truncated to 300 characters -- Recorded figure - not investment advice.Change rowNote to No figures were taken: the data source reply did not come back as price, change, currency and as-of. Reply was: answerText truncated to 300 characters -- Recorded figure - not investment advice.Change summaryLine to symbol: no figures could be read from the data source reply; it is kept in the sheet.Change summaryLine to symbol: no figures could be read from the data source reply; it is kept in the sheet.If a step below fails, save the error as writeErrorIf a step below fails, save the error as writeErrorTry
Google SheetsAdd a row to sheetName in spreadsheetUrl.Add a row to sheetName in spreadsheetUrl.On error
Change problem to the row could not be added to the sheet (writeError.message).Change problem to the row could not be added to the sheet (writeError.message).If problem is emptyIf problem is emptyYes
Add summaryLine to the end of figureLines and save it as figureLines.Add summaryLine to the end of figureLines and save it as figureLines.No
No steps
No
No steps
If problem isn't emptyIf problem isn't emptyYes
Add symbol - problem to the end of skippedLines and save it as skippedLines.Add symbol - problem to the end of skippedLines and save it as skippedLines.Show in the output: Nothing recorded for symbol: problem.Show in the output: Nothing recorded for symbol: problem.No
No steps
No
No steps
Set report to blank.Set report to blank.If figureLines isn't emptyIf figureLines isn't emptyYes
Change report to Watchlist figures recorded at recordedAt:Change report to Watchlist figures recorded at recordedAt:
figureLines joined by a new line.No
Change report to No figures were recorded at recordedAt.Change report to No figures were recorded at recordedAt.If skippedLines isn't emptyIf skippedLines isn't emptyYes
Change report to reportChange report to report
Not recorded this run:
skippedLines joined by a new line.No
No steps
Change report to reportChange report to report
These are recorded figures only. They were transcribed from the data source set up in this workflow, have not been checked against an exchange or a broker, and are not investment advice - no recommendation is intended or implied.Show in the output: report.Show in the output: report.If emailReportTo isn't emptyIf emailReportTo isn't emptyYes
If a step below fails, save the error as emailErrorIf a step below fails, save the error as emailErrorTry
GmailSend an email to emailReportTo.Send an email to emailReportTo.
Subject: Watchlist figures - runDatereportOn error
Show in the output: The report could not be emailed (emailError.message); the rows are in the sheet.Show in the output: The report could not be emailed (emailError.message); the rows are in the sheet.No
No steps