Today, I developed a personal bot using only Google Apps Script (GAS) that compiles stock prices, financial data, brokerage reports, and news into a sentiment analysis report named "Gemini" when a stock ticker is inputted via Telegram. From conceptualization to deployment, including bug fixes, the entire process was completed within a single session, though as anticipated, significant challenges arose during deployment and data parsing stages. Here’s a step-by-step account of the process.
1. Design Phase — Finalizing Sheet Schema and Gemini Prompts
Before coding, two key decisions needed to be made. Firstly, regarding the Google Sheets schema: between a cumulative history accumulation method that builds new rows for each request or maintaining only one row per asset with upsert, the cumulative method was chosen to track the temporal changes in target prices and qualitative assessments. While creating views via QUERY would suffice for viewing only the latest state later, opting for upsert would permanently forego historical data, influencing this decision.
Secondly, the input/output specifications of the Gemini Master Prompt: financial figures are inputted in JSON format, with five qualitative assessment criteria (profitability, risk, liquidity, cash flow, market sensitivity) received as an array, specifically arranged in order of importance. A structured output (responseSchema) ensures parsing accuracy by enforcing a specific format, eliminating parsing failure risks. Additionally, recent one-month news articles are included in the input data.
2. Integration of KIS API Endpoints and Acquisition of KRX Stock Data
Investigated the actual endpoints of Korea Investment Securities (KIS) Open API to finalize TR_ID for stock prices, balance sheets, income statements, and profitability ratios. Given token revocation frequency limitations, the system was designed to cache tokens via CacheService.
Corrected master data for typographical errors in stock names was compiled from the KRX Listed Companies List, totaling 2,652 entries comprising KOSPI 834 and KOSDAQ 1,818 companies, organized into a CSV format with columns for ticker, name, normalized name, and market classification. This data is integrated into a Master sheet, utilizing Levenshtein distance for similarity matching, with fallback to Gemini for ambiguous cases.
3. Deployment of GAS and Integration with Telegram Webhook
The bot was newly issued through BotFather to ensure compatibility with existing asset notification bots and webhooks. Attempting to batch upload 11 `.gs` files to Clasp encountered a roadblock. The command `clasp create` resulted in an "Apps Script API is disabled" error, and even after enabling it, `--type webapp` was rejected due to incorrect values (the correct option is standalone). Notably, web apps are deployment options rather than creation types.
Webhook registration failed due to clasp run requiring a separate API deployment, so I directly invoked Telegram's setWebhook using curl as a workaround.
4. Initial Performance Test, and Battle Against Bugs
I sent a request to Samsung Electronics but received no response. Directly POSTing to the web app URL resulted in a 302 Redirect status code, which Telegram couldn't handle, leading to the error message "Wrong response from the webhook: 302 Found". This issue stemmed from the GAS ContentService response; switching to HtmlService resolved it, yielding a successful 200 OK status immediately.
The next challenge was a series of missteps. The Gemini model name had been deprecated (404), and all values were showing as zero. The issue stemmed from the stock code "005930" being truncated to "5930" in the sheet, causing leading zeros to disappear; thus, a safeguard was implemented to ensure six-digit codes. Financial figures initially appeared drastically reduced in revenue, but this was due to a quarterly reset illusion in Korean consolidated financial statements ("cumulative year-to-date"). To address this, comparisons were adjusted separately for YoY revenue and QoQ balance sheets. Navigating inconsistencies, Naver Finance exhibited varying encodings across pages (UTF-8/EUC-KR mixed), and news retrieval failed without Referer headers, often flagged as bots and yielding empty results. For charts, initially aiming to implement candlestick charts directly, reliance shifted to utilizing existing daily candlestick images served by Naver due to GAS Charts limitations.
5. Web App Expansion Architecture (Option A)
Due to limitations in conveying tables and time series effectively via Telegram messages, a web application utilizing the same GAS backend was designed instead. Recognizing that workload distribution remains uneven even when files or projects are segmented (GAS bundles all .gs files, quotas are account-based), Option A was finalized by integrating a doGet method within a single project to invoke google.script.run. Additional features include dividend yield, foreign ownership ratio, industry PER ratios, and investor trading trends (market sentiment). For trading trends, given LLM's limitations with numerical table operations, trend analysis will be pre-computed on the server and relayed. Similarly, U.S. benchmark indicators will showcase representative industry stocks (e.g., semiconductor memory → Micron) as leading indicators before market openings, tailored by sector.
Trouble Shooting
| Symptoms | Cause | Solution method |
|---|---|---|
| Bot unresponsive, log shows 302 Found error. | Gas ContentService response triggers redirect, Telegram fails to receive 302 | Replace doPost response with HtmlService.createHtmlOutput |
| Clasp creation failed | Disabling Apps Script API + Invalid value for --type webapp | Enable API in usersettings and use --type standalone |
| Gemini Call Failed (404) | Gemini-2.5-Flash model deprecated | Replace with gemini-3.1-flash-lite |
| All prices are zero. | Sheet converts "005930" to the number 5930 (leading zeros omitted). | Restore with zero-padding to 6 digits before lookup |
| Shown as a sharp drop in sales | Profit and Loss Statement reflects cumulative values for the period, creating a quarterly reset illusion. | Profit and loss compared YoY, balance sheet compared QoQ by separation of comparison criteria. |
| News always empty list | No Referer header indicates the user is likely a bot from Naver. | Add "finance.naver.com" Referer header |
| Candle chart creation not possible | No Candlestick Builder in GAS Charts service | Fetch and deliver the Naver daily candlestick chart image directly. |
Reflections / Next Steps
External data sources, such as KIS response fields and Naver page structures, proved significantly more accurate than the document itself for real-time responses. Notably, pitfalls like "cumulative illusion bias" and "encoding discrepancies per page" highlighted the necessity of validating against original sources through test functions, ensuring efficiency. Deploying with a fixed `deploymentId` while incrementing versions maintained consistent webhook URLs effectively. For the next session, we plan to refactor `handleStockQuery_` into a pure data retrieval function, enabling shared core functionality between bots and web apps, alongside integrating five additional datasets. Context documentation will be finalized in an handover document for continuity in the subsequent session.
Comments
Post a Comment