tag:blogger.com,1999:blog-807194736985461945.post2229486913238481544..comments2022-04-09T00:55:33.434-04:00Comments on Slow Wealth: Fetching Stock Quotes with Excel 2013 and Windows 8Danhttp://www.blogger.com/profile/00335145797083190270noreply@blogger.comBlogger188125tag:blogger.com,1999:blog-807194736985461945.post-30153158031169444462020-08-24T00:20:16.127-04:002020-08-24T00:20:16.127-04:00This comment has been removed by a blog administrator.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-73247938935175487782020-04-01T05:44:31.913-04:002020-04-01T05:44:31.913-04:00Microsoft released a feature to Office 365 that al...Microsoft released a feature to Office 365 that allows Excel users to pull real-time stock prices into their spreadsheets.<br />https://www.thespreadsheetguru.com/blog/add-real-time-stock-prices-and-metrics-to-excel<br />Chris Wonghttps://www.blogger.com/profile/13530544416814235302noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-89706203831199587072020-03-30T19:45:24.341-04:002020-03-30T19:45:24.341-04:00Perhaps this is nothing new to members of the blog...Perhaps this is nothing new to members of the blog, but it has been a long time since I last worked on fixing my spreadsheet so that I can update the stock prices using control-alt-F9. This is what I came up with:<br /><br />=(WEBSERVICE(CONCATENATE(Y2,J17,"/quote/",W16,"?token=",Y1)))<br /><br />Where the contents of the Excell cells are as follows:<br />Y2 = https://cloud.iexapis.com/stable/stock/<br />For AT&T<br />J17 = T<br />W16 = latestPrice<br />Y1 = YOUR_TOKEN<br /><br />This has also worked to get the latest price for my mutual funds.<br />However, I have not been successful in getting other parameters such as the dividend yield or the dividend amount per share.Glennhttps://www.blogger.com/profile/00324167942857605125noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-61576841220346244662019-07-02T14:54:37.053-04:002019-07-02T14:54:37.053-04:00Has anyone developed a formula to get the dividend...<br />Has anyone developed a formula to get the dividend for all of the stocks in their portfolio? Modifying the formula Wegmand posted on June 21, 2019, I have found the following formula that works for many, but not all, of my stocks:<br /><br />=NUMBERVALUE(WEBSERVICE(CONCATENATE($B$16,B21,"/stats/",$C$17,"?token=",$B$15)))<br /><br />Note that /quote/ in Wegmand's formula was changed to /stats/<br /><br />$B$16 = https://cloud.iexapis.com/stable/stock/<br />B21 = T<br />$C$17 = dividendYield<br />$B$15 = YOUR_TOKEN<br /><br />That formula gives the annual percent yield (for this AT&T example and it would give about a 6% yield).<br /><br />Sometimes there is more than one way to get the same information. So, has anyone found a different formula to get the annual dividend yield (either percent or dollar amount)?Glennhttps://www.blogger.com/profile/00324167942857605125noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-8719986685597544522019-06-21T19:41:05.960-04:002019-06-21T19:41:05.960-04:00Thank you John and Wegmand! I was able to make Jo...Thank you John and Wegmand! I was able to make John's solution work on my spreadsheet. Thank you for help!<br /><br />One last question, is there a ticker for the NASDAQ and SP500 besides the ETF versions?<br /><br />Timhttps://www.blogger.com/profile/08391206858797972555noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-76270126327816363812019-06-21T17:47:14.450-04:002019-06-21T17:47:14.450-04:00Tim,
Download and examine the Excel sample file fr...Tim,<br />Download and examine the Excel sample file from the "Download it here" link just below your example formula on the same page: https://iexcloud.io/docs/api/#excel-how-to<br /><br />You'll have to use the NUMBERVALUE, WEBSERVICE, and CONCATENATE functions in the formula to build the textual reference to the data page. You're formula will eventually look something like this one that I use:<br /><br />=NUMBERVALUE(WEBSERVICE(CONCATENATE($B$16,B21,"/quote/",$C$17,"?token=",$B$15)))<br /><br />$B$16 = https://cloud.iexapis.com/stable/stock/<br />B21 = AAPL<br />$c$17 - latestPrice<br />$B$15 = YOUR_TOKEN<br /><br />Copy the formula above for each cell containing a ticker value (B21 in my example).wegmandhttps://www.blogger.com/profile/09061292218829041464noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-4846770755122471432019-06-21T17:41:33.178-04:002019-06-21T17:41:33.178-04:00Tim, replace aapl with "&A1&" in...<br />Tim, replace aapl with "&A1&" including the quote marks in the WEBSERVICE call.<br />John Gordonhttps://www.blogger.com/profile/11842961894629868621noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-27632315224232605732019-06-21T16:52:01.064-04:002019-06-21T16:52:01.064-04:00Does anyone know if it works on preferred stocks?Does anyone know if it works on preferred stocks?odelltrclanhttps://www.blogger.com/profile/08354952171471803465noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-81719821324130729592019-06-21T16:47:07.895-04:002019-06-21T16:47:07.895-04:00Hello. Sorry for being slow... I was able to mak...Hello. Sorry for being slow... I was able to make <br /><br />=WEBSERVICE("https://cloud.iexapis.com/stable/stock/aapl/quote/latestPrice?token=YOUR_TOKEN_HERE") <br /><br />work in Excel but unable to reference a cell such as A1. How do I do that? A1 contains the ticker symbol.Timhttps://www.blogger.com/profile/08391206858797972555noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-2998014140454965902019-06-10T21:53:42.220-04:002019-06-10T21:53:42.220-04:00Just tried it for mutual funds and it works great!...Just tried it for mutual funds and it works great! Thank you!MarkShttps://www.blogger.com/profile/06960027808922964658noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-54505124977026941762019-06-03T20:08:53.561-04:002019-06-03T20:08:53.561-04:00I was able make this work, too. It is nice that m...I was able make this work, too. It is nice that mutual funds are included now. However, beware, the mutual fund end of day prices are not updated at the 6pm Eastern posting deadline time like they are on yahoo and other sites. Today, after 8pm, the mutual fund prices are still the prior day's closing prices. I don't know what time overnight the correct daily mutual fund closing prices are posted, but they are there the next morning.J.R.https://www.blogger.com/profile/02498905571658181569noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-28492923968493445102019-06-03T19:12:31.077-04:002019-06-03T19:12:31.077-04:00Good news, indeed.
I can confirm the post above. I...Good news, indeed.<br />I can confirm the post above. I made the changes to the API calls and it began working today, including for mutual funds. Indexes like SP500, Nasdaq and the Dow must be updated manually. The presentation at https://iexcloud.io/ is not particularly clear, but if you follow the steps in the post above, it should work at least for fetching security prices. I had to modify some simple functions I have embedded in my Excel spreadsheets that work with the prices returned by iex.<br />John Gordonhttps://www.blogger.com/profile/11842961894629868621noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-86080347625786979052019-06-03T18:43:12.923-04:002019-06-03T18:43:12.923-04:00I notice that this thread remains dormant until th...I notice that this thread remains dormant until there's a change in where we get our data, then there's a flurry of activity. I'll start it off this time...<br /><br />If you've been using the WEBSERVICE from iextrading.com, you'll notice that it doesn't work anymore. BUT, they've updated everything so you'll just have to change your Excel formulas to keep getting the data. A few of the things that have changed:<br /><br />1. You'll have to sign up for a FREE account to get a required API token. Start here:<br />https://iexcloud.io/<br />You can use a secret token or a publishable one, but it must be included in each cell where you use the WEBSERVICE function.<br /><br />2. Read about how to use the new data in Excel here:<br />https://iexcloud.io/docs/api/#excel-how-to<br />There's a sample Excel file on the site that you can download to examine the formulas.<br /><br />3. Good news!! Mutual Fund quotes are supported!wegmandhttps://www.blogger.com/profile/09061292218829041464noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-74673269350656917542018-03-23T08:56:05.760-04:002018-03-23T08:56:05.760-04:00Chris: in general, yes, but it depends on the exch...Chris: in general, yes, but it depends on the exchange, you can ask the support directly about the particular exchange and even symbols: support@eodhistoricaldata.com, usually you will get an answer in several hours or less.Denis Alaevhttps://www.blogger.com/profile/14377407837494866010noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-71009636414606368592018-03-22T23:15:14.217-04:002018-03-22T23:15:14.217-04:00does eod support penny stocks (overseas exchanges)...does eod support penny stocks (overseas exchanges) with 3 decimal places?Chris Wonghttps://www.blogger.com/profile/13530544416814235302noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-1034378169774424762018-03-16T15:32:18.182-04:002018-03-16T15:32:18.182-04:00Dan Griffin: Iextrading supports only around 5500-...Dan Griffin: Iextrading supports only around 5500-6000 most active US stocks. Basically, only stocks that are traded on this exchange. eodhistoricaldata.com supports all 45 000 US symbols + foreign exchanges.Denis Alaevhttps://www.blogger.com/profile/14377407837494866010noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-62074253069872313382018-03-16T15:21:43.276-04:002018-03-16T15:21:43.276-04:00Yep -- that's what I've been doing as well...Yep -- that's what I've been doing as well. Probably not every night -- just once or twice a week at most. Just seems like there has to be a way. Thanks for the update.Danhttps://www.blogger.com/profile/00335145797083190270noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-7760907690454616962018-03-16T15:20:02.644-04:002018-03-16T15:20:02.644-04:00Dan Griffin: No more info since we last discussed...Dan Griffin: No more info since we last discussed the mutual fund issue here on Nov 8, 2017. I still have my mutual fund tickers permanently in my brokerage’s web site watch list. I export the watch list tickers and prices each night, and copy and paste them into my stock spreadsheet. Since the fund prices change only once a day, I only have to do this copy-and-paste exercise only once a day. I'm happy with this method to get my mutual fund info into my stock spreadsheet.Glennhttps://www.blogger.com/profile/00324167942857605125noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-62726888741159759362018-03-16T14:45:25.504-04:002018-03-16T14:45:25.504-04:00Do any of these options support mutual fund quotes...Do any of these options support mutual fund quotes? I've been typing in the mutual fund quotes manually for the last several months.Danhttps://www.blogger.com/profile/00335145797083190270noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-88644488971416248812018-03-16T14:39:15.342-04:002018-03-16T14:39:15.342-04:00fausto: You’re welcome. We all design our spread...fausto: You’re welcome. We all design our spreadsheets differently. My sheet uses the iextrading.com information for the following items: company name, sector, previous day’s close, current price, PE, 52 week lo, 52 week hi, ex-div date, date & time of latest price update, and annual dividend per share. Let us know if you need any of these. At a minimum, I suggest you use the date & time of the latest price update. That tells you if the website is working properly. If there has been no change to the date & time of the latest price update on your sheet after you press Ctl/Alt/F9, that tells you something is wrong (unless the stock price doesn’t change much during the day due to a low trading volume).Glennhttps://www.blogger.com/profile/00324167942857605125noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-86498848652483567782018-03-16T14:03:44.900-04:002018-03-16T14:03:44.900-04:00Thanks, Glenn! That worked for me! I have been s...Thanks, Glenn! That worked for me! I have been stuck without either price or name formulas ever since Yahoo killed its links. I had been following a lot of stocks and entering data by hand was overwhelming. I didn't know what my problem was until I stumbled across this discussion. You guys are lifesavers.faustohttps://www.blogger.com/profile/08858053354116695746noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-49100462449940388942018-03-16T08:33:00.790-04:002018-03-16T08:33:00.790-04:00fausto, I have been using this. The ticker symbol...fausto, I have been using this. The ticker symbol would be in cell B68 in this example:<br /><br />=(MID(WEBSERVICE("https://api.iextrading.com/1.0/stock/"&B68&"/quote?filter=companyName"),15,LEN(WEBSERVICE("https://api.iextrading.com/1.0/stock/"&B68&"/quote?filter=companyName"))-15))Glennhttps://www.blogger.com/profile/00324167942857605125noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-24130441590572748692018-03-16T08:32:05.743-04:002018-03-16T08:32:05.743-04:00From Fundamentals API Endpoint: https://eodhistori...From Fundamentals API Endpoint: https://eodhistoricaldata.com/knowledgebase/stock-etfs-fundamental-data-feeds/Denis Alaevhttps://www.blogger.com/profile/14377407837494866010noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-34000666538529581712018-03-16T08:31:12.411-04:002018-03-16T08:31:12.411-04:00Then you need something like this:
=WEBSERVICE(&q...Then you need something like this:<br /><br />=WEBSERVICE("http://eodhistoricaldata.com/api/fundamentals/AAPL.US?api_token=OeAFFmMliFG5orCUuwAKQ8l4WWFQ67YX&filter=General::Name")Denis Alaevhttps://www.blogger.com/profile/14377407837494866010noreply@blogger.comtag:blogger.com,1999:blog-807194736985461945.post-5958197224615707162018-03-16T08:27:14.236-04:002018-03-16T08:27:14.236-04:00Denis, your formula takes the ticker symbol and re...Denis, your formula takes the ticker symbol and returns the price. I'm looking for a formula that takes the ticker and returns the name of the company.faustohttps://www.blogger.com/profile/08858053354116695746noreply@blogger.com