Last active
March 30, 2024 05:58
-
-
Save bosslee/45e76f105963c7de159ddefd304a4ad3 to your computer and use it in GitHub Desktop.
Pulling Singapore stock data fromYahoo Finance into Google Sheets, use this as your custom function
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /** | |
| * Pull data from Yahoo Finance. | |
| * | |
| * @param {String} symbol The Ticker | |
| * @param {String} attribute The option such as "name" | |
| * @return yahoo finance stock data of the ticker | |
| * @customfunction | |
| * 1/ determine how to get the data | |
| * 2/ parse the data | |
| * 3/ ask for the data | |
| * 4/ return the data | |
| * | |
| * Credits | |
| * https://stackoverflow.com/questions/44030983/yahoo-finance-url-not-working | |
| * https://investmentmoats.com/uncategorized/solution-to-yahoo-finance-data-not-refreshing-in-google-spreadsheet/ | |
| * | |
| * For more google sheets tips can visit https://www.youtube.com/watch?v=3BTaRDdw6MM&list=PLCGu9Sxa6X66JBwBfvv8Hk2xqcb4pjF5- | |
| */ | |
| function YahooFinanceSG(symbol,attribute) { | |
| symbol = symbol || "G3B.SI"; | |
| symbol = encodeURI(symbol); // so that it works with a url | |
| Utilities.sleep(Math.floor(Math.random() * 5000)) | |
| // turn it into an URL and call it | |
| var url = 'https://query2.finance.yahoo.com/v7/finance/options/'+ symbol; | |
| Logger.log(url); | |
| // We get the data and the data return is in this variable call response | |
| var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true}); | |
| var responseCode = response.getResponseCode(); | |
| Logger.log(response); | |
| // Parsing the data | |
| if (responseCode === 200) { | |
| var chain = JSON.parse(response.getContentText()); | |
| Logger.log(parseFloat(chain.optionChain.result[0].quote.fiftyTwoWeekLow)); | |
| Logger.log(parseFloat(chain.optionChain.result[0].quote.fiftyTwoWeekHigh)); | |
| if (attribute === "name") { | |
| return chain.optionChain.result[0].quote.shortName; | |
| } else if (attribute === "change") { | |
| return parseFloat(chain.optionChain.result[0].quote.regularMarketChange); | |
| } else if (attribute === "changepct") { | |
| return parseFloat(chain.optionChain.result[0].quote.regularMarketChangePercent); | |
| } else if (attribute === "dayhigh") { | |
| return parseFloat(chain.optionChain.result[0].quote.regularMarketDayHigh); | |
| } else if (attribute === "daylow") { | |
| return parseFloat(chain.optionChain.result[0].quote.regularMarketDayLow); | |
| } else if (attribute === "52wlow") { | |
| return parseFloat(chain.optionChain.result[0].quote.fiftyTwoWeekLow); | |
| } else if (attribute === "52whigh") { | |
| return parseFloat(chain.optionChain.result[0].quote.fiftyTwoWeekHigh); | |
| } else if (attribute === "pe") { | |
| return parseFloat(chain.optionChain.result[0].quote.forwardPE); | |
| }else { | |
| return parseFloat(chain.optionChain.result[0].quote.regularMarketPrice); | |
| } | |
| }else{ | |
| return -1; // error handling | |
| } | |
| } | |
| // ETF | |
| // {optionChain={result=[{options=[], hasMiniOptions=false, underlyingSymbol=G3B.SI, strikes=[], expirationDates=[], quote={regularMarketDayLow=3.22, fullExchangeName=SES, bidSize=0.0, exchangeTimezoneShortName=SGT, ytdReturn=3.8, longName=Nikko AM Singapore STI ETF, fiftyTwoWeekRange=2.45 - 3.25, symbol=G3B.SI, fiftyDayAverage=3.067222, bid=3.21, exchange=SES, currency=SGD, fiftyTwoWeekHighChangePercent=-0.009230761, regularMarketPrice=3.22, twoHundredDayAverageChangePercent=0.122116454, firstTradeDateMilliseconds=1.2354372E12, shortName=Nikko AM STI ETF, marketState=POSTPOST, averageDailyVolume10Day=567038.0, trailingThreeMonthNavReturns=5.18, fiftyTwoWeekHigh=3.25, regularMarketTime=1.617181451E9, messageBoardId=finmb_59694105, twoHundredDayAverageChange=0.35042262, exchangeDataDelayedBy=0.0, market=sg_market, fiftyDayAverageChange=0.15277791, fiftyDayAverageChangePercent=0.049809862, regularMarketVolume=364590.0, esgPopulated=false, exchangeTimezoneName=Asia/Singapore, regularMarketChangePercent=-0.61728334, quoteSourceName=Delayed Quote, fiftyTwoWeekLow=2.45, averageDailyVolume3Month=820811.0, gmtOffSetMilliseconds=2.88E7, twoHundredDayAverage=2.8695774, quoteType=ETF, fiftyTwoWeekHighChange=-0.029999971, regularMarketDayRange=3.22 - 3.25, ask=3.22, askSize=0.0, triggerable=false, fiftyTwoWeekLowChangePercent=0.3142857, tradeable=false, regularMarketDayHigh=3.25, regularMarketChange=-0.01999998, fiftyTwoWeekLowChange=0.77, region=US, regularMarketPreviousClose=3.24, sourceInterval=20.0, trailingThreeMonthReturns=5.08, language=en-US, priceHint=4.0, regularMarketOpen=3.24}}], error=null}} | |
| // Stock | |
| // {"optionChain":{"result":[{"underlyingSymbol":"OV8.SI","expirationDates":[],"strikes":[],"hasMiniOptions":false,"quote":{"language":"en-US","region":"US","quoteType":"EQUITY","quoteSourceName":"Delayed Quote","triggerable":false,"currency":"SGD","firstTradeDateMilliseconds":1313629200000,"priceHint":4,"regularMarketChange":0.0,"regularMarketChangePercent":0.0,"regularMarketTime":1617181817,"regularMarketPrice":1.54,"regularMarketDayHigh":1.54,"regularMarketDayRange":"1.52 - 1.54","regularMarketDayLow":1.52,"regularMarketVolume":3003400,"regularMarketPreviousClose":1.54,"bid":1.53,"ask":1.54,"fullExchangeName":"SES","financialCurrency":"SGD","regularMarketOpen":1.54,"averageDailyVolume3Month":4300987,"averageDailyVolume10Day":3623914,"fiftyTwoWeekLowChange":0.37,"marketState":"POSTPOST","exchange":"SES","shortName":"Sheng Siong","longName":"Sheng Siong Group Ltd","messageBoardId":"finmb_136821806","exchangeTimezoneName":"Asia/Singapore","exchangeTimezoneShortName":"SGT","gmtOffSetMilliseconds":28800000,"market":"sg_market","esgPopulated":false,"fiftyTwoWeekLowChangePercent":0.31623933,"fiftyTwoWeekRange":"1.17 - 1.86","fiftyTwoWeekHighChange":-0.32000005,"fiftyTwoWeekHighChangePercent":-0.17204304,"fiftyTwoWeekLow":1.17,"fiftyTwoWeekHigh":1.86,"earningsTimestamp":1596020340,"earningsTimestampStart":1596020340,"earningsTimestampEnd":1596020340,"trailingAnnualDividendRate":0.065,"trailingPE":16.73913,"trailingAnnualDividendYield":0.042207792,"sharesOutstanding":1503539968,"bookValue":0.248,"fiftyDayAverage":1.5477142,"fiftyDayAverageChange":-0.0077142715,"fiftyDayAverageChangePercent":-0.0049842997,"twoHundredDayAverage":1.5895745,"twoHundredDayAverageChange":-0.049574494,"twoHundredDayAverageChangePercent":-0.031187274,"marketCap":2315451392,"priceToBook":6.209677,"sourceInterval":20,"exchangeDataDelayedBy":0,"epsTrailingTwelveMonths":0.092,"tradeable":false,"symbol":"OV8.SI"},"options":[]}],"error":null}} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Demo google sheet link
https://docs.google.com/spreadsheets/d/1GyMR-djZwEd-uazK4tgWHn7CrP5k80Hz8CHRf_BWKck/view