How to download data?

Discuss your favourite picks, broker, and trading or investment style.
User avatar
big easy
Veteran Contributor
Veteran Contributor
Posts: 1737
Joined: 27 Jul 2006 11:57
Location: Vancouver BC

Re: How to download data?

Post by big easy »

I have all my data in a legacy excel spreadsheet that I'd rather not recreate in Google Sheets. Anyhow I figured out a work around. I "published" my google sheet to a web address. Then I query that web address in excel using the Data > From Web command to download the google sheets data to my excel spreadsheet. It updates each time I open the spreadsheet with the latest quotes from google.

It's explained here, except I don't use their power query just the Data>From Web command to setup a query.
"Everybody has a plan until they get punched in the face." Mike Tyson
User avatar
Arby
Veteran Contributor
Veteran Contributor
Posts: 3125
Joined: 20 Feb 2005 19:23
Location: Ottawa, ON

Re: How to download data?

Post by Arby »

gobsmack wrote: 03 Nov 2017 15:19 Hmm.. after taking a second look, I may have jumped the gun a bit. It is downloading quotes now instead of giving me an error. However, the quotes do not appear to be up to date. So it may still require some tweaking.
Thanks for the instructions. I found the same problem as you mentioned. The downloaded quotes appear to be the low for the past few weeks, rather than the current quote.
User avatar
Bylo Selhi
Veteran Contributor
Veteran Contributor
Posts: 29494
Joined: 16 Feb 2005 10:36
Location: Waterloo, ON
Contact:

Re: How to download data?

Post by Bylo Selhi »

For people who are looking for a replacement for GETYAHOO or the SMF add-in now that Yahoo has killed their interface:

How to get quotes from GoogleFinance/GoogleSheets into LibreOffice.

I imagine something similar will work with Excel. I don't have a copy of that product to test with.
Sedulously eschew obfuscatory hyperverbosity and prolixity.
User avatar
big easy
Veteran Contributor
Veteran Contributor
Posts: 1737
Joined: 27 Jul 2006 11:57
Location: Vancouver BC

Re: How to download data?

Post by big easy »

Bylo Selhi wrote: 05 Nov 2017 11:37 For people who are looking for a replacement for GETYAHOO or the SMF add-in now that Yahoo has killed their interface:

How to get quotes from GoogleFinance/GoogleSheets into LibreOffice.

I imagine something similar will work with Excel. I don't have a copy of that product to test with.
That's more or less what I did up thread in excel. It seems to work, although I will not know until Monday if the quotes refresh.

BTW, the Yahoo quote for TD US Index e fund was broken for a week or two before the whole thing crapped out. Too many issues with Yahoo.
"Everybody has a plan until they get punched in the face." Mike Tyson
Oldpos
Newcomer
Newcomer
Posts: 1
Joined: 07 Nov 2017 21:02

Re: How to download data?

Post by Oldpos »

Thanks guys. I am sure many would appreciate someone sharing a fix to the Yahoo problem. A sample file would be ideal. I hope I am not skint too much. Like others, I don’t really want to start from scratch with something else as I like Excel’s conditional formatting ability.
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Getting Internet stocks quotes into Excel-current status?

Post by Hogwild »

Hi everyone:

I can't remember exactly which methods, if any, are still working to enable you to get automatic online stock quotes into Excel. I remember Yahoo disabled the API or something similar. Are any methods still possible? BTW, I'm using Office 2007, if that matters, but if really necessary, I have a package with 2010 here I could install.

Hogwild
User avatar
Arby
Veteran Contributor
Veteran Contributor
Posts: 3125
Joined: 20 Feb 2005 19:23
Location: Ottawa, ON

Re: Getting Internet stocks quotes into Excel-current status?

Post by Arby »

See the thread How to Downlaod Data, starting at this post.

Thread merged by Moderator
User avatar
Shakespeare
Veteran Contributor
Veteran Contributor
Posts: 23396
Joined: 15 Feb 2005 23:25
Location: Calgary, AB

Re: How to download data?

Post by Shakespeare »

So does the Yahoo portfolio kluge give up-to-date data? (I use the Yahoo .csv download from My Portfolio and import it into 1-2-3, then use lookup functions.)
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

Arby:

Thanks, but....that thread just confused the bejeebers out of me. I'm IT-savvy, but not Excel-savvy and I can't code at all. What is the bottom line? From what I read, Google Finance is still a viable source? And if so, how do I get that stuff into my sheet? Is there a really simple tutorial somewhere?
BRIAN5000
Veteran Contributor
Veteran Contributor
Posts: 9063
Joined: 08 Jun 2007 23:27

Re: How to download data?

Post by BRIAN5000 »

Shakespeare wrote: 08 Nov 2017 16:53 So does the Yahoo portfolio kluge give up-to-date data? (I use the Yahoo .csv download from My Portfolio and import it into 1-2-3, then use lookup functions.)
I've come up with a bogus work around for myself takes 20-30 seconds not as easy as pushing a button as before. Data from Yahoo is delayed you can export "a live" CSV file from Advanced Dashboard if you want will work the same.

I download CSV file remove all extraneous columns sort alphabetically paste into a column, my old spreadsheet now pulls info from that column, good for end of day or once in the morning.

Considering setting up portfolio in Portfolio manager at TDDI or just shooting myself. :evil:
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

Brian:

While I myself would be fine with that, my sister will be using this workbook, and she's not what you'd call tech-savvy.

Oh, and right on with that comment about shooting myself (yourself) etc. It should be way easier nowadays to be able to move data around in different programs. The brokers don't seem to care at all, and it's a shame, cause it might help some of us to make more money.


:-)
User avatar
Shakespeare
Veteran Contributor
Veteran Contributor
Posts: 23396
Joined: 15 Feb 2005 23:25
Location: Calgary, AB

Re: How to download data?

Post by Shakespeare »

I download CSV file remove all extraneous columns sort alphabetically paste into a column, my old spreadsheet now pulls info from that column, good for end of day or once in the morning.
It's easier than that.

Import the csv to a separate table on a new sheet. Clear the table beforehand. Hide uneccessary columns. You can use a macro to sort at the push of a button if you need to - 123 doesn't care if the lookup column isn't presorted. Use lookup functions to pull the data.

All I do is log into Yahoo Finance (Globeinvestor will also work), push the download button, save the file overwriting the earlier version, open the 123 file, and press one button.

Anyway, if you aren't day trading, why do you need quotes several times a day?
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
chufinora
Contributor
Contributor
Posts: 769
Joined: 12 Oct 2009 15:03
Location: Ottawa

Re: How to download data?

Post by chufinora »

Here is a link to an (adapted) spreadsheet (Google sheet) I use to track my portfolio daily changes



Just select make a copy (You will need to be logged into a google account to do this) under file tab

Change ticker symbols, and the amount of stocks for each one and 'voila'

Each time you open the sheet - the sheet will update with the latest quote values (or will update periodically if you leave it open)

(And to Oldpos if you look at the sheet, it has conditional formatting just like Excel)
User avatar
Shakespeare
Veteran Contributor
Veteran Contributor
Posts: 23396
Joined: 15 Feb 2005 23:25
Location: Calgary, AB

Re: How to download data?

Post by Shakespeare »

As posted by Phrasingweb and verified Portfolios deprecation information - Finance Help, the portfolio function from Google Finance is being closed.
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

Can anyone suggest a resource on how to do this directly in Excel?
User avatar
Insomniac
Veteran Contributor
Veteran Contributor
Posts: 2802
Joined: 29 Oct 2011 19:01
Location: Vancouver Island

Re: How to download data?

Post by Insomniac »

chufinora wrote: 08 Nov 2017 19:44 Here is a link to an (adapted) spreadsheet (Google sheet) I use to track my portfolio daily changes



Just select make a copy (You will need to be logged into a google account to do this) under file tab

Change ticker symbols, and the amount of stocks for each one and 'voila'

Each time you open the sheet - the sheet will update with the latest quote values (or will update periodically if you leave it open)

(And to Oldpos if you look at the sheet, it has conditional formatting just like Excel)
:thumbsup:

This should still work OK after Google drops the portfolios feature. AFAIK the Google Spreadsheets are not affected as Google says they will continue to provide stock quotes.
chufinora
Contributor
Contributor
Posts: 769
Joined: 12 Oct 2009 15:03
Location: Ottawa

Re: How to download data?

Post by chufinora »

Yes the Google finance functions are totally separate from the Google finance portfolio thing that is now deprecated
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

Sorry to keep carping on it but...any way to do this directly in Excel?
User avatar
Shakespeare
Veteran Contributor
Veteran Contributor
Posts: 23396
Joined: 15 Feb 2005 23:25
Location: Calgary, AB

Re: How to download data?

Post by Shakespeare »

Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

EDIT: Finally found the ogres-crypt.com weblink and the top-level directory for the plugin.
If anyone knows of a HOWTO for use with Google for people with no coding skills, please speak up.


Shakespeare:



Took a quick look at the SMF add-in. Isn't it designed to work with (the now disabled) Yahoo feed? AFAICT, it works with Google Finance, yes?

Does one need a bit of coding skills to use this, cause I'm hopeless at coding.
Last edited by Hogwild on 09 Nov 2017 13:44, edited 1 time in total.
User avatar
AltaRed
Veteran Contributor
Veteran Contributor
Posts: 33398
Joined: 05 Mar 2005 20:04
Location: Ogopogo Land

Re: How to download data?

Post by AltaRed »

WADR, consider buying an annual $90 subscription to Quicken and spend your valuable time elsewhere. It has served me well for over 20 years. I've never quite understood 'make work' projects.
Imagefiniki, the Canadian financial wiki The go-to place to bolster your financial freedom
User avatar
Insomniac
Veteran Contributor
Veteran Contributor
Posts: 2802
Joined: 29 Oct 2011 19:01
Location: Vancouver Island

Re: How to download data?

Post by Insomniac »

AltaRed wrote: 09 Nov 2017 12:25 WADR, consider buying an annual $90 subscription to Quicken and spend your valuable time elsewhere. It has served me well for over 20 years. I've never quite understood 'make work' projects.
Many of us enjoy DIY. It's educational and good exercise for the brain. Good thing to do on a day in the crappy Canadian Winter!
BRIAN5000
Veteran Contributor
Veteran Contributor
Posts: 9063
Joined: 08 Jun 2007 23:27

Re: How to download data?

Post by BRIAN5000 »

AltaRed wrote: 09 Nov 2017 12:25 WADR, consider buying an annual $90 subscription to Quicken and spend your valuable time elsewhere. It has served me well for over 20 years. I've never quite understood 'make work' projects.
Quicken doesn't have any of my priority algorithms or show stocks that are buys or sells or close to it and light them up in pretty colours. :roll:
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
User avatar
AltaRed
Veteran Contributor
Veteran Contributor
Posts: 33398
Joined: 05 Mar 2005 20:04
Location: Ogopogo Land

Re: How to download data?

Post by AltaRed »

BRIAN5000 wrote: 09 Nov 2017 17:13
AltaRed wrote: 09 Nov 2017 12:25 WADR, consider buying an annual $90 subscription to Quicken and spend your valuable time elsewhere. It has served me well for over 20 years. I've never quite understood 'make work' projects.
Quicken doesn't have any of my priority algorithms or show stocks that are buys or sells or close to it and light them up in pretty colours. :roll:
Fair enough, but your brokerage account website should provide you with Alert capabilities and/or the Alert options in your Level 2 trading software will do that. Both my BMO IL and Scotia iTrade trading platforms provide me with that, be it price (cross, or over/under), or volume, bid, ask, change, % change or.....
Imagefiniki, the Canadian financial wiki The go-to place to bolster your financial freedom
User avatar
Shakespeare
Veteran Contributor
Veteran Contributor
Posts: 23396
Joined: 15 Feb 2005 23:25
Location: Calgary, AB

Re: How to download data?

Post by Shakespeare »

Hogwild wrote: 09 Nov 2017 12:01 EDIT: Finally found the ogres-crypt.com weblink and the top-level directory for the plugin.
If anyone knows of a HOWTO for use with Google for people with no coding skills, please speak up.


Shakespeare:



Took a quick look at the SMF add-in. Isn't it designed to work with (the now disabled) Yahoo feed? AFAICT, it works with Google Finance, yes?

Does one need a bit of coding skills to use this, cause I'm hopeless at coding.
No, it uses the Yahoo portfolio view. Look at the example spreadsheet.
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
Post Reply