Please Note: Blog posts are not selected, edited or screened by Seeking Alpha editors.

Updating Financial Data In Your Excel (Part 1)

I have noticed that some colleagues in SA ask on how updating the information related to their portfolio or watchlist. In this series of INSTABLOG I will tell you the 3 methods I use and how you could do it as well. The first one to obtain data from Web sites which allow you to download data in Excel (for example, Morningstar.com). I assume you have already one spreadsheet to manage your investments. In following INSTABLOGS I will share with you how you can download data from (1) Webs which does not have Excel download feature, and (2) Webs where information you need is spread in many pages.

In short I have my own Excel spreadsheet and (every day) I imput hundreds of parameters from the portfolio I created in Morningstar. To do it, I login in Morningstar, click the export to excel on my portfolio, and then open my spreadsheet in Excel and hit Alt-Ctrl-T. That's it. In a few second my data is ready.No matter how simple or complex your spreadsheet is, It is very easy, and anyone could use it.

First, let me tell you what you will need to do in Morningstar (morningstar.com)

1- Creation of portfolio: You will need to use an account (a free account works) in Morningstar to introduce the information on each stock you have.

2- Customization of Morningstar data: Morningstar provides an extensive number of parameters which you can customize in up to four different views ("My view 1",.."My view 4").

The screen below shows customized screen "My view 3" of my portfolio, which I called "USSTOCK". You need to remember the name of your portfolio as it will be the name of the file created by Morningstar

In my case, I use 3 views, so I have USSTOCK_MyView1.xls, USSTOCK_MyView2.xls and USSTOCK_MyView3.xls). For this example, I assume just on view (so one morningstar file called USSTOCK_MyView1) and then, once you understand how it works, you may follow the same procedure to add more views if you need them.

Second point is your spreadsheet and how to upload the data from the portfolio you have created in Morningstar.

To do this, you will need to add one new sheet into your spreadsheet. You may call it "Data Web Morningstar". The goal now is to copy/paste the information provided by Morningstar in "USSTOCK_MyView1.xls" into your excel. (if you use more views, you can copy corresponding files one after another).

Your spreadsheet will have other sheets which contain your data and operations. All you have to do is to refer your spreadsheet data to the corresponding information which will be available in sheet "Data Web Morningstar".

This is how "Data Web Morningstar" will look like in my excel:

To update your data, you can use a Macro that will go to " USSTOCK_MyView1.xls", copy the information and paste it in "Data Morningstar". I call my Macro "BringData". Here you have a short version that may help you:

------------------------------------------------------

Sub BringData

' BringData Macro

' Direct Access: Ctrl+Shift+T

'

Sheets("Data Web Morningstar").Select

Range("A3").Select

Windows("USSTOCK_MyView1.xls").Activate

Range("A2:t41").Select

Selection.Copy

Windows("PORTFOLIO.xlsm").Activate

'

End Sub

-----------------------------------------------------

Once you have completed these steps, as I said, whenever you want to update data in your spreadsheet, you only need to (1) go to Morningstar.com, select your customized view, and click "Export" (upper right corner), (2) In the Download folder open "USSTOCK_MyView1" and allow edditing, and finally (3) you open your spreadsheet and hit keys Shift+Ctrl+T.

Just one additional hint. Some day you may want to change the data in your portfolio Morningstar (for example, buy or sell some stock). That will make a change in the file "USSTOCK_MyView1" which will translate to "Data Web Morningstar" and can mess up all your spreadsheet. To prevent that, the best is to add an additional sheet in your Excel, may call it "Values", whose function will be to interface between "Data Web Morningstar" and your other sheets with data and formulas. In this way, any change in Morningstar will only affect "Data Web Morningstar" and you will be able to correct it, just by adjusting "Values". This is how it looks "Values" in my spreadsheet.

Finally, let me show you how it looks one of the main pages in my spreadsheet, the one I call "Analysis". All its information is related to data available in "Values"

I hope you find this helpful. Of course, I would not recommend you to try, unless you have some practice with Windows Excel.

If you have any question or comment, will be glad to take care.