google spreadsheet function, 'GoogleFinance'
google spreadsheet function, 'GoogleFinance'
Hi all,
I just learned of a google spreadsheet function called "GoogleFinance." It can look up various information of a stock/mutual fund ticker. So in order to find out my true asset allocation I have each account's holdings in my spreadsheet.
e.g.
Ticker, # of shares, and share price (I can load this dynamically via =GoogleFinance("VNQ"; "Price")), total holding amount
this is really helpful for up to date information. All I have to do is change the number of shares quarterly to see how my AA is doing.
Anyone else have helpful spreadsheet functions?
I just learned of a google spreadsheet function called "GoogleFinance." It can look up various information of a stock/mutual fund ticker. So in order to find out my true asset allocation I have each account's holdings in my spreadsheet.
e.g.
Ticker, # of shares, and share price (I can load this dynamically via =GoogleFinance("VNQ"; "Price")), total holding amount
this is really helpful for up to date information. All I have to do is change the number of shares quarterly to see how my AA is doing.
Anyone else have helpful spreadsheet functions?
- hoppy08520
- Posts: 2193
- Joined: Sat Feb 18, 2012 10:36 am
Re: google spreadsheet function, 'GoogleFinance'
Yes, a great utility. See http://support.google.com/docs/bin/answ ... ic/1360898 page for more uses. I like the "expenseratio" feature and the undocumented "name" feature which returns the fund name.
Re: google spreadsheet function, 'GoogleFinance'
I've been using the Google spreadsheet and it's "GoogleFinance" extensively to track my investments / plan my retirement to the point I am beginning to max out the spreadsheet size and number of functions. I switched to Google after problems with MSN Stock quotes stopped working for me (is this still broke???).
One thing I wish they had was a "Dividend" feature. (e.g. it would return .51 for KO). Alas it is not a perfect world....
In the mean time I've been using the Yahoo ImportData function, then doing a VLookup. Not pretty, but it works
One thing I wish they had was a "Dividend" feature. (e.g. it would return .51 for KO). Alas it is not a perfect world....
In the mean time I've been using the Yahoo ImportData function, then doing a VLookup. Not pretty, but it works
Re: google spreadsheet function, 'GoogleFinance'
Same here. I no longer have to manually update the price info to see how far I'm straying from my AA; I just load the spreadsheet. Great feature, and I'll be happy when some of the other functions (like expenseratio) work with ETFs.vandizzle wrote: this is really helpful for up to date information. All I have to do is change the number of shares quarterly to see how my AA is doing.
Re: google spreadsheet function, 'GoogleFinance'
I did not know about that "name" function. That is awesome. I just incorporated it into my spreadsheet and it works great.hoppy08520 wrote:Yes, a great utility. See http://support.google.com/docs/bin/answ ... ic/1360898 page for more uses. I like the "expenseratio" feature and the undocumented "name" feature which returns the fund name.
My only wish is for a way to automatically update share quantity to account for reinvested distributions.
Re: google spreadsheet function, 'GoogleFinance'
Thanks for posting. I had been doing this manually in Excel!
Re: google spreadsheet function, 'GoogleFinance'
I use M* portfolio tracker with the items I wish to track in the M*. I use two different views actually for this. I set up an excel spread sheet with one page for my master allocation and variation tracking using present price, last 12 months return and other items for my tracking purposes. In that excel work book is a page for each holding where I record buy/sell/re-invest, prices, nember or shares, etc. Current number of shares is imported from each pages holding into the allocation tracker page. I download the M* page, click enable editing and the master allocation page in my asset manager spread sheet is updated with present values, variation from target, etc., as well as anything else I would like to track. Click, click, done!.
Re: google spreadsheet function, 'GoogleFinance'
I want to use this function but most of my savings are in my 401K plan and it doesn't give ticker symbols for the investments....should I be able to get this information or do some 401K funds not have ticker symbols?
50% S&P 500 IDX ER .01% |
10% Ext Mkt ER .04% |
10% Small Cap Value ER .15% |
20% International TM ER .08% |
10% Vang Total Bond Market ER .03%
Re: google spreadsheet function, 'GoogleFinance'
They may be collective trusts:ef11 wrote:I want to use this function but most of my savings are in my 401K plan and it doesn't give ticker symbols for the investments....should I be able to get this information or do some 401K funds not have ticker symbols?
http://thefinancebuff.com/collective-tr ... rence.html
Re: google spreadsheet function, 'GoogleFinance'
Choose an equivalent fund or ETF and pretend you have an equivalent amount of that.ef11 wrote:I want to use this function but most of my savings are in my 401K plan and it doesn't give ticker symbols for the investments....should I be able to get this information or do some 401K funds not have ticker symbols?
For example, let's say you have the following you your 401k:
$23,732.65 in BlackRock US Equity Market Index-F
$9,611.93 in BlackRock US Debt Index-T
BlackRock US Equity Market Index is reasonably identical to Vanguard Total Stock Market ETF (VTI), which closed at $70.28/share Friday -- $23,732.65 / $70.28 = 337.69 shares of VTI, so that's what you enter on your spreadsheet.
BlackRock US Debt Index is reasonably identical to Vanguard Total Bond Market ETF (BND), which closed at $83.93/share on Friday -- $9611.93/$89.93 = 106.88 share of BND.
This will yield very accurate results for a while, but will eventually star to drift out of sync (because of the different expense ratios, different distribution schedules, tracking error, etc) so you should re-calibrate at least once a year, preferably right before you do your regular account "maintenance" (i.e. rebalancing, yearly IRA contribution).
When doing this, make sure you're comparing apples to apples -- i.e. make sure all amounts above reflect Friday's closing prices; if you're dividing Thursday by Friday (i.e. because your 401k website is slow to update) you're going to have a bad time. You could do this during the week but it's more difficult. I recommend doing this Sunday or maybe early Monday morning, as you should be reasonably certain you're seeing Friday's closing data by that point.
If your non-401k portfolio consists mostly of ETFs, I'd suggest choosing ETFs for your equivalents as I did above. Even though your 401k holdings don't have published intraday values, this lets you see a reasonable approximation, and keeps your balance from being all wamperjawed if you check while the market is open. If you only hold traditional funds rather than ETFs, you'd be best off picking traditional funds for your equivalents.
Re: google spreadsheet function, 'GoogleFinance'
You can update prices in Excel with an add-in if you don't want to post your portfolio online. http://www.bogleheads.org/forum/viewtopic.php?t=75476ataloss wrote:Thanks for posting. I had been doing this manually in Excel!
Harry Sit has left the forums.
Re: google spreadsheet function, 'GoogleFinance'
Here's a screenshot of my spreadsheet:
Only the light yellow fields are editable, everything else is auto-calculated.
The "Variation" column has conditional formatting to show how far out of balance you are (yellow/orange/red).
Only the light yellow fields are editable, everything else is auto-calculated.
The "Variation" column has conditional formatting to show how far out of balance you are (yellow/orange/red).
Re: google spreadsheet function, 'GoogleFinance'
Here is onevandizzle wrote:Hi all,
I just learned of a google spreadsheet function called "GoogleFinance." It can look up various information of a stock/mutual fund ticker. So in order to find out my true asset allocation I have each account's holdings in my spreadsheet.
[...]
Anyone else have helpful spreadsheet functions?
- Porcupine
Re: google spreadsheet function, 'GoogleFinance'
The addiction of using "googlefinance" is that it allows me to hit just one button on my smartphone and instantaneously I can see the value of my total portfolio by using Google Documents.
This addition is worse than watching CNBC and the ups and downs of the market. Of course if you own any mutual funds you only get the thrill of one or two updated values a day.
In my google spreadsheet I have added graphics...so I can see my Bonds/Stocks ratio graphically (I know, big deal).
This addition is worse than watching CNBC and the ups and downs of the market. Of course if you own any mutual funds you only get the thrill of one or two updated values a day.
In my google spreadsheet I have added graphics...so I can see my Bonds/Stocks ratio graphically (I know, big deal).
Re: google spreadsheet function, 'GoogleFinance'
Very cool to know about. Also interesting to see how some folks have created some spreadsheets around this.
Re: google spreadsheet function, 'GoogleFinance'
Some additional Googlefinance features you may like...
=googlefinance(".dji","price") ---- looks up the current value of the Dow Jones Industrial Average
=googlefinance(".dji","change") ---- price change in DJIA
=googlefinance(".dji","changepct")/100 ---- and the percent change since last close of DJIA
Simlarly, you can add .INX for the S&P 500.
No more relying on other stock quote websites, I can display the data how I wish.
=googlefinance(".dji","price") ---- looks up the current value of the Dow Jones Industrial Average
=googlefinance(".dji","change") ---- price change in DJIA
=googlefinance(".dji","changepct")/100 ---- and the percent change since last close of DJIA
Simlarly, you can add .INX for the S&P 500.
No more relying on other stock quote websites, I can display the data how I wish.
Re: google spreadsheet function, 'GoogleFinance'
Thanks for posting. I'm keeping an eye on things like this in anticipation of the future. Right now Google spreadsheets are undeveloped in many ways, and things still work fine in Quicken, so will see how it goes. -- Tet
Re: google spreadsheet function, 'GoogleFinance'
Similar to mine, except I do without columns I, L, M, N, and O.SSSS wrote: The "Variation" column has conditional formatting to show how far out of balance you are (yellow/orange/red).
Re: google spreadsheet function, 'GoogleFinance'
Google spreadsheets are wonderful and almost unlimited in what you can track and/or calculate. There are some master spreadsheet jockeys over on the M* forum if you are a member there that have some good 'public samples' that you can download and build off of.
If you are used to working in excel and writing formulas you can customize to your own liking.
Two features that I have written formulas for are stock dividends and bond interest to help me project annual income.
Good luck!
If you are used to working in excel and writing formulas you can customize to your own liking.
Two features that I have written formulas for are stock dividends and bond interest to help me project annual income.
Good luck!
Re: google spreadsheet function, 'GoogleFinance'
Although I don't use the 'GoogleFinance' function myself (since nearly all our investments are in our 401k's, neither of which employ any listed funds) I agree that Google Spreadsheets are incredibly helpful for tracking investments. I set ours up so I can simply copy/paste the current account balances from our 401k websites into a sheet and then it will extract all the pertinent information - share price, shares, balances, etc. - and give me an updated allocation across all our accounts. Very handy for rebalancing (I set up another worksheet to do this) across all the accounts, too.frequentT wrote:Google spreadsheets are wonderful and almost unlimited in what you can track and/or calculate. There are some master spreadsheet jockeys over on the M* forum if you are a member there that have some good 'public samples' that you can download and build off of.
If you are used to working in excel and writing formulas you can customize to your own liking.
Two features that I have written formulas for are stock dividends and bond interest to help me project annual income.
Good luck!
Re: google spreadsheet function, 'GoogleFinance'
Have been using it in all of my portfolio management spreadsheets for some time.
I also have a spreadsheet that I use to get a market overview. First section is US stock ETFs, next international ETFs, then bond ETFs. I use ChangePct, with conditional formatting that shows different shades of blue (up) or red (down) depending on magnitude of percent change. I don't pay much attention to it unless there has been an extended up or down market, in which case I tend to rebalance on days when there are dark shades of red or blue.
Also, you can use a VG ETF to get an idea about the percent change in the corresponding VG mutual fund at the close. Probably not worth it to most people, but it can help in more precise rebalancing if you enter your mutual fund orders shortly before the close, especially on big up or down days. This was useful, for example, last year when the REIT fund dropped about 10% in one day. VNQ told me it was a day to add to VGSLX, and gave me a pretty good idea of how much to add. Of course you could do this without the GoogleFinance function, but it makes it convenient to work with multiple funds in a single spreadsheet.
Kevin
I also have a spreadsheet that I use to get a market overview. First section is US stock ETFs, next international ETFs, then bond ETFs. I use ChangePct, with conditional formatting that shows different shades of blue (up) or red (down) depending on magnitude of percent change. I don't pay much attention to it unless there has been an extended up or down market, in which case I tend to rebalance on days when there are dark shades of red or blue.
Also, you can use a VG ETF to get an idea about the percent change in the corresponding VG mutual fund at the close. Probably not worth it to most people, but it can help in more precise rebalancing if you enter your mutual fund orders shortly before the close, especially on big up or down days. This was useful, for example, last year when the REIT fund dropped about 10% in one day. VNQ told me it was a day to add to VGSLX, and gave me a pretty good idea of how much to add. Of course you could do this without the GoogleFinance function, but it makes it convenient to work with multiple funds in a single spreadsheet.
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: google spreadsheet function, 'GoogleFinance'
I thought some might be interested in seeing the "market quotes" spreadsheet I mentioned earlier.
Here are stock ETFs as of today:
Note that you also can get TradeTime, and from that compute how old the quote is (I believe this is updated every minute in the spreadsheet, so the TradeTime could be up to a minute older than shown).
Note the slightly darker shade of blue for VGK (Europe). For the conditional formatting of the background color for stocks I use 1% bands, from < -4% to > 4%. You can only use ten conditions (last I checked).
I convert TradeTime to Pacific time with this formula: =GoogleFinance($B2,G$1)-1/8. I discovered that you convert TradeTime using the time zone difference in hours as a fraction of 24 hours, so 3 hours (PT vs ET) = 3/24 = 1/8.
The formula for "Age" is =(now())-G2.
Here are bond ETFs as of today:
Since bonds are less volatile than stocks, for the conditional formatting of the background color I use 0.5% bands from < -2% to > 2%.
Note that you also can get historical quotes, and easily create a chart of the results (only part of the data is shown):
The formula for historical quotes used here is: =googlefinance(A68,"close","01/01/12",today()). I added the historical ChgPct calculation.
Kevin
Here are stock ETFs as of today:
Note that you also can get TradeTime, and from that compute how old the quote is (I believe this is updated every minute in the spreadsheet, so the TradeTime could be up to a minute older than shown).
Note the slightly darker shade of blue for VGK (Europe). For the conditional formatting of the background color for stocks I use 1% bands, from < -4% to > 4%. You can only use ten conditions (last I checked).
I convert TradeTime to Pacific time with this formula: =GoogleFinance($B2,G$1)-1/8. I discovered that you convert TradeTime using the time zone difference in hours as a fraction of 24 hours, so 3 hours (PT vs ET) = 3/24 = 1/8.
The formula for "Age" is =(now())-G2.
Here are bond ETFs as of today:
Since bonds are less volatile than stocks, for the conditional formatting of the background color I use 0.5% bands from < -2% to > 2%.
Note that you also can get historical quotes, and easily create a chart of the results (only part of the data is shown):
The formula for historical quotes used here is: =googlefinance(A68,"close","01/01/12",today()). I added the historical ChgPct calculation.
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: google spreadsheet function, 'GoogleFinance'
Does anyone know if this spreadsheet function uses the Google Finance API? This is important bc the API has been deprecated and will be removed completely in October:
https://developers.google.com/finance/
I couldn't find any mention of this in the spreadsheet function documentation. My guess is that they are still managing the database but are just pulling the cord on public access via the web API. I guess it's a good way to get people to use Google Docs instead of rolling their own portfolio management tool.
I started down the road of rolling my own tool using a python script and yahoo finance queries, but the hard part is determining/characterizing funds by their asset class/subclass. I think I'll try the spreadsheet. Thanks for the hint.
https://developers.google.com/finance/
I couldn't find any mention of this in the spreadsheet function documentation. My guess is that they are still managing the database but are just pulling the cord on public access via the web API. I guess it's a good way to get people to use Google Docs instead of rolling their own portfolio management tool.
I started down the road of rolling my own tool using a python script and yahoo finance queries, but the hard part is determining/characterizing funds by their asset class/subclass. I think I'll try the spreadsheet. Thanks for the hint.
- hoppy08520
- Posts: 2193
- Joined: Sat Feb 18, 2012 10:36 am
Re: google spreadsheet function, 'GoogleFinance'
I paniced after reading this, but googling around, I found the following which seems to suggest that the GoogleFinance() function in Google Spreadsheets will still be supported:masrapido wrote:Does anyone know if this spreadsheet function uses the Google Finance API? This is important bc the API has been deprecated and will be removed completely in October:
https://developers.google.com/finance/
I couldn't find any mention of this in the spreadsheet function documentation. My guess is that they are still managing the database but are just pulling the cord on public access via the web API. I guess it's a good way to get people to use Google Docs instead of rolling their own portfolio management tool.
I started down the road of rolling my own tool using a python script and yahoo finance queries, but the hard part is determining/characterizing funds by their asset class/subclass. I think I'll try the spreadsheet. Thanks for the hint.
http://social.microsoft.com/Forums/en/m ... 628dc6ff96
From this linked page above:
Karolina Netolicka, Google Finance Product Manager wrote: we could serve more people better by integrating the data into other Google products rather than requiring them to write code to access the data. For example, check out the GoogleFinance() function in Google Spreadsheets which replicates some of the API's functionality without requiring you to write code.
Re: google spreadsheet function, 'GoogleFinance'
Nice find, hoppy! As I suspected, it sounds like the Google Finance API is indeed alive and well behind the scenes. I'll stop writing code and give the Google finance spreadsheet a shot.
- LazyNihilist
- Posts: 1005
- Joined: Sat Feb 19, 2011 8:56 pm
Re: google spreadsheet function, 'GoogleFinance'
I love GoogleFinance function and google spreadsheet. I use it to track investments and check when to rebalance.
The strong do what they can and the weak suffer what they must -Thucydides
- LazyNihilist
- Posts: 1005
- Joined: Sat Feb 19, 2011 8:56 pm
Re: google spreadsheet function, 'GoogleFinance'
As langkawi noted, they may be collective trusts and GoogleFinance only has publicly traded stocks/funds.ef11 wrote:I want to use this function but most of my savings are in my 401K plan and it doesn't give ticker symbols for the investments....should I be able to get this information or do some 401K funds not have ticker symbols?
Here is an example of one of the 401k fund my company uses
https://institutional.vanguard.com/VGAp ... 1385059599
It has no ticker symbol, it is the equivalent of Vanguard TR 2020. But it's prices are different from the publicly traded VTWNX.
I use GoogleFinance for publicly traded funds, and for this fund, I do an automated screen scraper program to retrieve the value.
If your 401k is one of the Vanguard TR funds, I can give you the small python script to retrieve the value.
The strong do what they can and the weak suffer what they must -Thucydides
Re: google spreadsheet function, 'GoogleFinance'
Can't you just retrieve price for TR 2020 and multiply by the scaling factor that gives you the price of your fund?LazyNihilist wrote:it is the equivalent of Vanguard TR 2020. But it's prices are different from the publicly traded VTWNX.
Kevin
If I make a calculation error, #Cruncher probably will let me know.
- LazyNihilist
- Posts: 1005
- Joined: Sat Feb 19, 2011 8:56 pm
Re: google spreadsheet function, 'GoogleFinance'
Yes that will work too, but sometimes you might be off by 1 or 2 cents. But we need to be absolutely accurate.Kevin M wrote:Can't you just retrieve price for TR 2020 and multiply by the scaling factor that gives you the price of your fund?LazyNihilist wrote:it is the equivalent of Vanguard TR 2020. But it's prices are different from the publicly traded VTWNX.
Kevin
The strong do what they can and the weak suffer what they must -Thucydides
Re: google spreadsheet function, 'GoogleFinance'
I did a search in this thread and didn't see any mention of the Google "ImportHTML" function. I'm able to scrape the price from the link you quoted above, using the following Google function:LazyNihilist wrote:As langkawi noted, they may be collective trusts and GoogleFinance only has publicly traded stocks/funds.ef11 wrote:I want to use this function but most of my savings are in my 401K plan and it doesn't give ticker symbols for the investments....should I be able to get this information or do some 401K funds not have ticker symbols?
Here is an example of one of the 401k fund my company uses
https://institutional.vanguard.com/VGAp ... 1385059599
It has no ticker symbol, it is the equivalent of Vanguard TR 2020. But it's prices are different from the publicly traded VTWNX.
I use GoogleFinance for publicly traded funds, and for this fund, I do an automated screen scraper program to retrieve the value.
If your 401k is one of the Vanguard TR funds, I can give you the small python script to retrieve the value.
=Index(ImportHTML(ʺhttps://institutional.vanguard.com/VGApp/iip/site/institutional/investments/price?strategy=1385059599ʺ,ʺtableʺ,11),4,2)
Hope this helps others.
Re: google spreadsheet function, 'GoogleFinance'
papoono, I get a parse error. One problem I've seen with this approach in my one or two experiments with it in the past is that the web page changes, so arguments must be modified. I'm wondering if this is the case here. I can verify that the URL works.
Kevin
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: google spreadsheet function, 'GoogleFinance'
Hi Kevin,Kevin M wrote:papoono, I get a parse error. One problem I've seen with this approach in my one or two experiments with it in the past is that the web page changes, so arguments must be modified. I'm wondering if this is the case here. I can verify that the URL works.
Kevin
When you copy and paste the formula, do the double quotes get substituted with a 'box' character like this □ ? Try pasting the formula into Notepad and see if this is the case, then substitute the 'box' characters with double quotes. I reproduced your parse error if I just try to copy the formula "as-is" from the post.
And yes, this is a rather fragile way of retrieving the data. In fact I this used to use this method to retrieve fund prices from uii.nysaves.s.upromise.com, and this used to work, but is now broken
Re: google spreadsheet function, 'GoogleFinance'
Nope, I see quotes, whether pasting directly into google docs spreadsheet or notepad. No big deal; I like this idea, due to fragility, don't use it anywhere. Last time I tried it was to get bond or CD quotes from the Fidelity site, and I had to modify it to work, and it couldn't retrieve all the quotes I needed anyway.papoono wrote:When you copy and paste the formula, do the double quotes get substituted with a 'box' character like this □ ? Try pasting the formula into Notepad and see if this is the case, then substitute the 'box' characters with double quotes. I reproduced your parse error if I just try to copy the formula "as-is" from the post.(
Thanks for sharing though; it's good for us google docs users to know about these tricks.
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: google spreadsheet function, 'GoogleFinance'
No real need for an add-in as you can simply create a web-query in Excel and import just about anything you like. Personally, I keep a list of securities in Google Finance (a "portfolio") and import that into my excel spreadsheet (automatically). AA, performance, accounts, gains, etc, are all tracked and presented in Excel.tfb wrote:You can update prices in Excel with an add-in if you don't want to post your portfolio online. http://www.bogleheads.org/forum/viewtopic.php?t=75476ataloss wrote:Thanks for posting. I had been doing this manually in Excel!
I prefer Excel as it's *much* more powerful than the Google app. Only downside though not a big one is the lack of cloud support. So I can't check my nw from my phone (shucks!).
Best,
Brad
- LazyNihilist
- Posts: 1005
- Joined: Sat Feb 19, 2011 8:56 pm
Re: google spreadsheet function, 'GoogleFinance'
papoono wrote: I did a search in this thread and didn't see any mention of the Google "ImportHTML" function. I'm able to scrape the price from the link you quoted above, using the following Google function:
=Index(ImportHTML(ʺhttps://institutional.vanguard.com/VGApp/iip/site/institutional/investments/price?strategy=1385059599ʺ,ʺtableʺ,11),4,2)
Hope this helps others.
papoono,
This is great stuff. I didn't know I could do this from google docs. Brilliant.
The strong do what they can and the weak suffer what they must -Thucydides
- hoppy08520
- Posts: 2193
- Joined: Sat Feb 18, 2012 10:36 am
Re: google spreadsheet function, 'GoogleFinance'
Here's another use of GoogleFinance, if you want to return closing share price on a particular date. This is useful for computing your holdings on a particular date in time:
Supposing you have a the date in a different cell, then you can reference the cell containing the date (assuming it's in cell B3):
And you'll commonly have the ticker symbol in a different cell as well (so you can see it), so in practice you'll use the function like this, assuming you have the ticker symbol in cell B2:
The reason you need to include the INDEX wrapper function around this is that without it, the function will expand into the neighboring cells like this:
Using the INDEX() wrapper with the ;2;2 arguments tells the INDEX function to return the value in the cell that is 2 over and 2 down, which is the actual closing price (117.59 in the example above).
Credit to this Google knowledgebase post that pointed this out to me.
Code: Select all
=INDEX(GoogleFinance("VFINX", "close", "12/31/2011") ;2;2 )
Code: Select all
=INDEX(GoogleFinance("VFINX", "close", B3) ;2;2 )
Code: Select all
=INDEX(GoogleFinance(B2 ,"close", B3) ;2;2 )
Code: Select all
Date | Close
------------------------------
1/3/2012 16:00:0 | 117.59
Credit to this Google knowledgebase post that pointed this out to me.
Re: google spreadsheet function, 'GoogleFinance'
GoogleFinance Guru's... In the last 2 to 3 days, my lookup for Dow Jones Industrials ( .dji ) and S&P 500 ( .inx ) have stopped working.
All other lookups work fine. Did Google change the syntax? Anyone else having similar experience?
This is my cell contents for example:
=googlefinance(".dji","price")
Thanks.
All other lookups work fine. Did Google change the syntax? Anyone else having similar experience?
This is my cell contents for example:
=googlefinance(".dji","price")
Thanks.
Re: google spreadsheet function, 'GoogleFinance'
seems to be working fine for me. I used the same forumula as you
for 8/1/2012 the values are 12,976.13 and 1,375.14 respectively
Code: Select all
=GoogleFinance(".DJI","price")
=GoogleFinance(".INX","price")
Re: google spreadsheet function, 'GoogleFinance'
Thanks pappono for the reply. Interesting, if I type the one formula into a new worksheet, it works.papoono wrote:seems to be working fine for me. I used the same forumula as you
for 8/1/2012 the values are 12,976.13 and 1,375.14 respectivelyCode: Select all
=GoogleFinance(".DJI","price") =GoogleFinance(".INX","price")
If I add the formula to an open cell on my existing worksheet, it does not work.
Reminder, this spreadsheet has worked for me daily for at least 2 years...on PC, Phone and Tablet never with a problem.
I use Chrome, but tried it on Firefox..still does not work. I also confirmed I get the same error on Android.
I did find a thread on this problem from two days ago on the Google Forums, with no resolution...but many people having same experience. Weird.
Thanks again for your help.
- hoppy08520
- Posts: 2193
- Joined: Sat Feb 18, 2012 10:36 am
Re: google spreadsheet function, 'GoogleFinance'
FWIW, regarding "sometimes it works, sometimes it doesn't", I too have found GoogleFinance to be a little glitchy and inconsistent from time to time.
Re: google spreadsheet function, 'GoogleFinance'
There's a limit on the number of calls to GoogleFinance you can make per spreadsheet, might you be hitting that limit?Igglesman wrote: Thanks pappono for the reply. Interesting, if I type the one formula into a new worksheet, it works.
If I add the formula to an open cell on my existing worksheet, it does not work.
Reminder, this spreadsheet has worked for me daily for at least 2 years...on PC, Phone and Tablet never with a problem.
I use Chrome, but tried it on Firefox..still does not work. I also confirmed I get the same error on Android.
I did find a thread on this problem from two days ago on the Google Forums, with no resolution...but many people having same experience. Weird.
Thanks again for your help.
Re: google spreadsheet function, 'GoogleFinance'
I use getquote("VNQ") with OpenSource, in the same way. If you have your dividends reinvested into your cash account, you don't have to update shares quarterly, and have some cash for re-balancing available.vandizzle wrote:Hi all,
I just learned of a google spreadsheet function called "GoogleFinance." It can look up various information of a stock/mutual fund ticker. So in order to find out my true asset allocation I have each account's holdings in my spreadsheet.
e.g.
Ticker, # of shares, and share price (I can load this dynamically via =GoogleFinance("VNQ"; "Price")), total holding amount
this is really helpful for up to date information. All I have to do is change the number of shares quarterly to see how my AA is doing.
Anyone else have helpful spreadsheet functions?
Even educators need education. And some can be hard headed to the point of needing time out.
Re: google spreadsheet function, 'GoogleFinance'
Google finance still shows RZV as a Rydex fund, rather than Guggenheim fund.hoppy08520 wrote:FWIW, regarding "sometimes it works, sometimes it doesn't", I too have found GoogleFinance to be a little glitchy and inconsistent from time to time.
Even educators need education. And some can be hard headed to the point of needing time out.
Re: google spreadsheet function, 'GoogleFinance'
This is possible...if they changed the limit in the last week... all the calls I do make to my index funds still work...so unless it is calls to "the averages" -- dow jones, S&P -- I don't think that is the answer.JamesSFO wrote:Igglesman wrote:
There's a limit on the number of calls to GoogleFinance you can make per spreadsheet, might you be hitting that limit?
I am following a google forums thread on this subject, I am not the only one experiencing this problem. It has been escalated to the "engineers".
Thanks for the help.
- hoppy08520
- Posts: 2193
- Joined: Sat Feb 18, 2012 10:36 am
Re: google spreadsheet function, 'GoogleFinance'
I think that's probably because some of the databases from which GoogleFinance pulls data are outdated. The names of those funds changed just a few months ago.rustymutt wrote:Google finance still shows RZV as a Rydex fund, rather than Guggenheim fund.hoppy08520 wrote:FWIW, regarding "sometimes it works, sometimes it doesn't", I too have found GoogleFinance to be a little glitchy and inconsistent from time to time.
http://www.indexuniverse.com/sections/n ... name-.html
I hope that at least the "price" is correct.
Re: google spreadsheet function, 'GoogleFinance'
Having the same problem In my Market Quotes spreadsheet. Attributes for all ETFs are being returned, but getting #N/A for .INX attributes. Attributes for SPY still work fine, so at least you can get a good estimate of percent change for the S&P 500 if you're interested in that.Igglesman wrote:GoogleFinance Guru's... In the last 2 to 3 days, my lookup for Dow Jones Industrials ( .dji ) and S&P 500 ( .inx ) have stopped working.
All other lookups work fine. Did Google change the syntax? Anyone else having similar experience?
This is my cell contents for example:
=googlefinance(".dji","price")
Thanks.
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: google spreadsheet function, 'GoogleFinance'
GoogleFinance once again is returning data for .INX (S&P 500 Index).
Kevin
Kevin
If I make a calculation error, #Cruncher probably will let me know.
Re: google spreadsheet function, 'GoogleFinance'
It bothered me that GoogleFinance doesn't work with ETF for expenseratio, so I spent some time playing with importHTML to get this. Here's a universal expense ratio function that works for both funds and etfs. Hope others will find this useful
A2 is the cell of the ticker e.g. VTI.
Code: Select all
=INDEX(SPLIT(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", A2), "table", 1),1,13), " "), 1, 2)
Re: google spreadsheet function, 'GoogleFinance'
I've been using this for years in my personal spreadsheets for prices (so I can see my net returns). Just saw that I am able to pull in additional data, such as "expenseratio". But when I use this it shows as 62% rather than .62% for example.
I am typing:
=GoogleFinance ("POAGX", "expenseratio")
The cell shows .62 but when I click format, number, percent - it shows as 62%, not .62%.
Anyone know how to "fix"?
I am typing:
=GoogleFinance ("POAGX", "expenseratio")
The cell shows .62 but when I click format, number, percent - it shows as 62%, not .62%.
Anyone know how to "fix"?
and btw, this works for expense ratios. However the same issue occurs for all other fields (like returns, etc.) Would be nice to just have the direct Google Finance fix - to show properly.Blue Jay wrote:It bothered me that GoogleFinance doesn't work with ETF for expenseratio, so I spent some time playing with importHTML to get this. Here's a universal expense ratio function that works for both funds and etfs. Hope others will find this usefulA2 is the cell of the ticker e.g. VTI.Code: Select all
=INDEX(SPLIT(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", A2), "table", 1),1,13), " "), 1, 2)
BH Contests: 23 #89 of 607 | 22 #512 of 674 | 21 #66 of 636 |20 #253/664 |19 #233/645 |18 #150/493 |17 #516/647 |16 #121/610 |15 #18/552 |14 #225/503 |13 #383/433 |12 #366/410 |11 #113/369 |10 #53/282
Re: google spreadsheet function, 'GoogleFinance'
The raw data the function returns is just "0.62" with no context specified, as the function only returns a numeric value, it has no way to directly specify that "0.62" means "0.62%"; perhaps they should have programmed the function to return "0.0062" instead, but they didn't. If you tell the spreadsheet to force formatting to "percentage", then displaying 0.62 as 62% is correct behavior.sperry8 wrote:I am typing:
=GoogleFinance ("POAGX", "expenseratio")
The cell shows .62 but when I click format, number, percent - it shows as 62%, not .62%.
You have at least a couple options to display it the way you want to:
1. Leave default formatting and modify your formula to concatenate a % to the end: =concatenate((googlefinance(B20,"expenseratio")),"%")
2. Divide by 100 so that forced formatting percentage will work as you want it to: =(googlefinance("POAGX","expenseratio"))/100