Canadian Money Forum banner

21 - 40 of 49 Posts

·
Registered
Joined
·
7,253 Posts
If you have Office 2007 or above, try saving as XLSX format.
That is the "open" format and should work with other spreadsheet programs.
 

·
Banned
Joined
·
3 Posts
Hi All,
I was looking for these. I even asked people from them but didn't get proper response.
Thanks I got them in this forum.
 

·
Registered
Joined
·
3 Posts
Excel based investment analysis

Thanks for the calculators Cannon_Fodder. Any chance of viruses in these before I enable the macros?
 

·
Registered
Joined
·
748 Posts
Investment Tracking Spreadsheet

Okay folks... I have been working on an Excel workbook to track stocks. It is a lot more complicated than I thought it would be to do this!

I'm posting it here so that others could use it. The only other one I've been able to come across is KaeJS', but since I do enjoy playing with Excel, thought I'd give a shot at it myself. There is a phoney account inputted already for demonstration.

The workbook is far from perfect, but it will work for me, for now. I don't have anything for options (as I don't fully understand them yet), share splits/reverse splits, things like that. There is no space for keeping track of taxes payable, though you should be able to figure it out using the dividend total and net profit columns in the ledger tab. Also, contributing and withdrawing cash part-way through the year throws off some of the gain/loss % calculations.

I am open to suggestions for how to improve it, and if there is enough interest I will update and create new versions for your use, then you can just copy/paste your info from the old tracking workbook into the new one. Maybe it is a good idea to subscribe to this thread so that you know when an updated version comes along, I really don't know yet how far this is going to go though.

v1.0 is a .xlsx file for Excel 2007 and 2010.

View attachment 90

v1.0a is a .xls file for Excel 97-2003. *I cannot guarantee this will work properly, as I made the workbook in Excel 2010!*

View attachment 91

I will post instructions on how to use the live quote sheet within the next few days. If anyone else knows an easier way to get live quotes, please share.
 

·
Registered
Joined
·
90 Posts
I like the spreadsheet, but needs a little importing quotes. Try this. Just keep adding stock tickers down column A (unlimited).

It downloads from Yahoo, which doesn't have perfect source data, so be careful in trusting the data as you would be careful in trusting yahoo data on their website. Lookup the correct ticker value there.

Only works with Excel 2007.
 

Attachments

·
Registered
Joined
·
748 Posts
Max, thanks for the tip, however it won't work for me. My computer won't let me run the macros due to security issues. Do you know how to create the macro to do this? I'm not too familiar with them. Ideally, I'd like to have something that will pull only the current market price straight into the cell on my "holdings" sheet.
 

·
Registered
Joined
·
90 Posts
Sub GetQuote()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
With ie
.navigate "http://download.finance.yahoo.com/d/quotes.csv?s=" & "IBM" & "&f=l1"
Do
DoEvents
Loop Until Not .busy
Do
DoEvents
Loop Until .Document.readystate = "complete"
Cells(1, 1) = .Document.body.innertext
End With
ie.Quit
End Sub

Cutting it down as simple as possible, this will get the current price for IBM into cell A1. Use a variable for the ticker symbol and change cells(1,1) to desired output range.

This should now work with earlier versions of Excel as well.
 

·
Banned
Joined
·
95 Posts
v1.0 is a .xlsx file for Excel 2007 and 2010.

View attachment 90

v1.0a is a .xls file for Excel 97-2003. *I cannot guarantee this will work properly, as I made the workbook in Excel 2010!*

View attachment 91

I will post instructions on how to use the live quote sheet within the next few days. If anyone else knows an easier way to get live quotes, please share.
It looks like both attachments are invalid!
 

·
Registered
Joined
·
748 Posts
In case anyone else is interested, I took the spreadsheets down because there wasn't any interest in it but if somebody is, PM me.
 

·
Registered
Joined
·
3,061 Posts
Question: Is there a formula to show current inflation rates (updated automatically online) for a given country using Google Docs or Excel, similar to the formulas we can use to get automatically updating stock prices?
 

·
Premium Member
Joined
·
2,686 Posts
Question: Is there a formula to show current inflation rates (updated automatically online) for a given country using Google Docs or Excel, similar to the formulas we can use to get automatically updating stock prices?
I don't think there is. You could process the table in this page to get inflation data for Canada but of course, if BoC changes the table, you'll have to change the code too.

http://www.bankofcanada.ca/rates/price-indexes/cpi/
 

·
Registered
Joined
·
3,089 Posts
Question: Is there a formula to show current inflation rates (updated automatically online) for a given country using Google Docs or Excel, similar to the formulas we can use to get automatically updating stock prices?
For Google Docs you can use: =GoogleFinance("CURRENCY:USDCAD" ; "average")
 

·
Registered
Joined
·
3,061 Posts
For Google Docs you can use: =GoogleFinance("CURRENCY:USDCAD" ; "average")
Thanks, but I don't think that's an inflation rate, right? It looks like a currency exchange rate or something.

I was looking for something that could display current inflation rates for a particular country and update them automatically.

I have historical average rates here: http://data.worldbank.org/indicator/FP.CPI.TOTL.ZG
 
21 - 40 of 49 Posts
Top