Canadian Money Forum banner

1 - 20 of 26 Posts

·
Registered
Joined
·
357 Posts
Discussion Starter #1 (Edited)
Hey, I use Excel for my Portfolio tracking. I am wondering, instead of me updating prices every few weeks manually, in 2018 is there a way to get Excel to update them for me. The prices for ETFs / Mutual Funds ect are all public info so this must exist in a slick little addon.

Anyone using such a thing ?

I see a few online with google, so there are options.

(Currently using Excel 2010)
 

·
Registered
Joined
·
357 Posts
Discussion Starter #2
Well,

After some work I have decided to use the Excel Quotes Add-In by Gaier Software - its free and seems to work find for what I need. There are a few items that it cant auto update for me but thats OK cause it can do like 80% of what I want.

What a time saver !!!!

Why didn't I do this before ?
 

·
Registered
Joined
·
412 Posts
FYI: Years ago, I converted my Excel financial tracker spreadsheet to Google Sheets to get access to automatic price updates. For example for VXC you use cell formula "=GOOGLEFINANCE("TSE:VXC", "price")". I also like accessing Google sheets from multiple laptops I have for home and travel. Good to know there are ways of doing it with Excel.
 

·
Banned
Joined
·
588 Posts
+1

Another Google Sheets user. Have all the prices automated. Also automated the exchange rate, gold price, etc..
Overall, works very well. (except when they change the ^*^*&^& syntax every once in a while !!)

Also appreciate being able to access it anywhere. Work, Home, phone even (when on vacation).
 

·
Registered
Joined
·
357 Posts
Discussion Starter #5
I am struggling to find a ticker for gold that works.... what are you guys using to establish a price for gold ? more specifically gold in CAD ?
 

·
Banned
Joined
·
588 Posts
This is what I use. I found it on the net somewhere. At the end, 5 refers to the number of ounces I currently have and F1 refers to the cell where I have the live spot exchange for USD/CAD.


=Index(ImportHTML("http://www.apmex.com/spotprices/gold-prices","table",9),2,2)*F1*5

 

·
Registered
Joined
·
412 Posts
I am struggling to find a ticker for gold that works.... what are you guys using to establish a price for gold ? more specifically gold in CAD ?
Try this:

=Index(ImportHTML("http://www.apmex.com/spotprices/gold-prices","table",9),2,2)*GoogleFinance("CURRENCY:USDCAD")
 

·
Registered
Joined
·
412 Posts
I tried to post it again and lost the fight to the CMF website from inserting that space. Here is an image of the formula and shows the space location. Click on capture.jpg.

Capture.JPG
 

·
Registered
Joined
·
5,223 Posts
google sheets always worked well for me though now i just paste them into an apple numers sheet and that takes 5 minutes every couple of weeks, easy enough
 

·
Registered
Joined
·
116 Posts
Hey, I use Excel for my Portfolio tracking. Anyone using such a thing ?(Currently using Excel 2010)
As mentioned by others, you can create a Google Sheets document with the quotes. THEN you can import that sheet into an Excel spreadsheet and refer to the quotes in other worksheets. I do this and it works great.
 

·
Registered
Joined
·
2,794 Posts
As mentioned by others, you can create a Google Sheets document with the quotes. THEN you can import that sheet into an Excel spreadsheet and refer to the quotes in other worksheets. I do this and it works great.
Yep, I converted my Excel Macros to access Google sheets about a year ago. Works great.

I click a button in Excel that downloads the new stock data from Google sheets. Then I click another button to update all the Excel sheets with that data. Simple.

ltr
 

·
Registered
Joined
·
357 Posts
Discussion Starter #17
K, So I just upgraded my Excel from 2010 to 2019 ! Way to get into the 21st century !

I was using the free Excel Quotes Add-In to import all the data I needed into my excel file that I have built......

Now, with my upgrade I see that Quotes (Gaier Software - Excel Quotes Add-In) doesnt support Excel 2019 !!! PITA...

Now I have a new problem.... I need to replace Excel Quotes and get something that works in Excel 2019.

* I am not looking for opinions on Google sheets or anything... I am trying to get price data into Excel 2019....


I have located these 3 programs and I am wondering if anyone here has used them or not ? (no specific order)

1 - Stock Connector (Stock Connector add-in for Excel)

2 - Deriscope (Deriscope | Options & Derivatives Pricing and Risk Valuation Freeware in Excel)

3 - Excel Price Feed (Real Time Financial and Stock Prices Add-in / Plug-in For Excel Spreadsheets - Excel Price Feed)

looking for feedback on the above 3 Excel Add Ons...
 

·
Registered
Joined
·
357 Posts
Discussion Starter #19
I just use Google Sheets - works fine for quotes into Excel.

ltr

ltr, so instead of using an addon for Excel you use google sheets ? I have read where you create a sheet with all the info you need in google sheets, then post it publicly, and then get the data with excel. Is that what you are doing ?

And... to update your excel file, do you need to go to google sheets and reload/update your google file then repost it publically ? So that excel can get the latest info ? seems like a lot of work vs an Excel Add On where all that is integrated, no ?

Thx for the perspective and the goal of staying with excel.

H
 

·
Registered
Joined
·
2,794 Posts
It's quite simple. There is no posting or public or anything for you to do once it's set up.. You just go to Google sheets and create a private spreadsheet that actually looks and acts like Excel. You enter the function in a cell just like Excel that will produce the latest price (for example) of the stock you want. i.e for Fortis the Cell would contain =GoogleFinance("TSE:FTS","PRICE").

You lay it out to return the price of all your stocks in question.

To get those prices into Excel I use the Web Query function. I have it do the query after I click a button in Excel, but you can have the query run on startup instead.

ltr
 
1 - 20 of 26 Posts
Top