Automatic MOS Calculator Watchlist for Microsoft Excel --- *Temporarily Offline*

[petekoch]

Originally Posted by Hobbes

The cash flow statement from MSN is a very dynamic page. There are 2 issues that I had. First, some companies have more categories than others...so the rows you are looking for move up and down. Second, if a company has less that 5 years of data the columns shift around.

I get the row numbers I am looking for by matching to the category names:
=MATCH("Cash from Operating Activities",A1:A100,0)
=MATCH("Purchase of Fixed Assets",A1:A100,0)
=MATCH("Total Cash Dividends Paid",A1:A100,0)

I get the column number by matching to the year:
=IF(ISERROR(MATCH(2006,B3:F3,0)),0,MATCH(2006,B3:F 3,0))
=IF(ISERROR(MATCH(2005,B3:F3,0)),0,MATCH(2005,B3:F 3,0))
...etc.

Then you can get the values using these indexes...for instance here would be the 5 years of operating cash. In this case cell I4 is the row number from the first example and I8,I9,I10, and I11 are the columns for 2005, 2004, 2003, and 2002 respectively...2006 is always in column B:
=INDEX(B$1:B$57,$I$4)
=IF(I8=2,INDEX(C$1:C$57,$I$4),IF(I8=3,INDEX(D$1$ 57,$I$4),IF(I8=4,INDEX(E$1:E$57,$I$4),IF(I8=5,INDE X(F$1:F$57,$I$4),"N/A"))))
=IF(I9=3,INDEX(D$1$57,$I$4),IF(I9=4,INDEX(E$1:E$ 57,$I$4),IF(I9=5,INDEX(F$1:F$57,$I$4),"N/A")))
=IF(I10=4,INDEX(E$1:E$57,$I$4),IF(I10=5,INDEX(F$1: F$57,$I$4),"N/A"))
=IF(I11=5,INDEX(F$1:F$57,$I$4),"N/A")

Good stuff. I've experienced the shifting rows; never noticed shifting columns.

I think an appropriate location for most of this is probably in MAIN, in the area of Cell "O44", near similar tables. Frankly, I was thinking of changing the CASH FLOW query to use the Morningstar cash flows (which are FREE -- not part of the Premium stuff, so no passwords complicating things). I'm using M* in another model I'm playing with. The advantage of M* in this case is that they hand you Free Cash Flow -- they do the calculations. But it's probably better (and more consistent) to stay with MSN in this case. I'll try plugging this in.

[petekoch]

Originally Posted by Hobbes

=MATCH("Purchase of Fixed Assets",A1:A100,0)

FWIW, you should be looking for a match on "Capital Expenditures". "Fixed Assets" are a subset of "Capital Expenditures".

Arrrgh.

[SeanieT]

This may seem stupid but how do I place all of these Formulas into my spreadsheet. Will I get a cash flow for each year?

[farriaza]

I had the same problem with errors with invalid web query....


Quote


Originally Posted by maxfisher05
This fixed the "Invalid Web Query" bug for me... let me know if this works for anyone else.

go to:

C:\Documents and Settings\Max\Local Settings\Temporary Internet Files

Make sure you have hidden files and folders set to visible, and obviously replace "Max" with "Your user name"

Select all. Then hit delete. If it asks you if you want to delete your cookies you can say no.

This worked for me, Im using XP SP2 and Office 07.

It worked for me.... thanks

[petekoch]

This fixed the moving rows problem for Cash Flow. It does not address the problem Hobbes mentions about the columns being erratic.

Cell O44 : ="'Cash Flow'!B" &($P$46) Cell P44 : ="'Cash Flow'!F" &($P$46)
Cell O45: ="'Cash Flow'!B" & ($P$47) Cell P45: ="'Cash Flow'!F" & ($P$47)

Cell O46: Match 3A
Cell P46:
=MATCH("Cash from Operating Activities",'Cash Flow'!A1:A100,0)

Cell O47: Match 3B
Cell P47:
=MATCH("Capital Expenditures",'Cash Flow'!A1:A100,0)

Cell O48: First Cash from Opns
Cells O49 through O52: Next Cash from Opns
Cell P48:
=INDEX(INDIRECT($O$44):INDIRECT($P$44), N48)
Cell P49:
=INDEX(INDIRECT($O$44):INDIRECT($P$44), N49)
Cell P50:
=INDEX(INDIRECT($O$44):INDIRECT($P$44), N50)
Cell P51:
=INDEX(INDIRECT($O$44):INDIRECT($P$44), N51)
Cell P52:
=INDEX(INDIRECT($O$44):INDIRECT($P$44), N52)

Cell O53: First Cap.Expndtrs.
Cells O54 through O57: Next Cap.Expndtrs.
Cell P53:
=INDEX(INDIRECT($O$45):INDIRECT($P$45), N53)
Cell P54:
=INDEX(INDIRECT($O$45):INDIRECT($P$45), N54)
Cell P55:
=INDEX(INDIRECT($O$45):INDIRECT($P$45), N55)
Cell P56:
=INDEX(INDIRECT($O$45):INDIRECT($P$45), N56)
Cell P57:
=INDEX(INDIRECT($O$45):INDIRECT($P$45), N57)

Cell N48: 5
Cell N49: 4
Cell N50: 3
Cell N51: 2
Cell N52: 1
Cell N53: 5
Cell N54: 4
Cell N55: 3
Cell N56: 2
Cell N57: 1

Cell G16:
=IF(P48+P53=0,"",IF(P48+P53="NA","",IF(P48+P53="", "",P48+P53)))
Cell H16:
=IF(P49+P54=0,"",IF(P49+P54="NA","",IF(P49+P54="", "",P49+P54)))
Cell I16:
=IF(P50+P55=0,"",IF(P50+P55="NA","",IF(P50+P55="", "",P50+P55)))
Cell J16:
=IF(P51+P56=0,"",IF(P51+P56="NA","",IF(P51+P56="", "",P51+P56)))
Cell K16:
=IF(P52+P57=0,"",IF(P52+P57="NA","",IF(P52+P57="", "",P52+P57)))

Notes:
Cells N48 through N57 are numbers. Simply type in a 5, e.g.

Cells O46 through O57 are merely text labels. Finger checks won't hurt you.

In Cells O45 and O46, that's a double apostraphe followed by a single apostraphe just before the words Cash Flow.

As I said above, this corrects the moving rows problem, but not the situation with columns. Maybe another day.

My spreadsheet works and I pasted the formulae into this message, so there shouldn't be any typos. Still, I suggest you make a backup copy of your sheet before entering these changes. Probably not for the feint of heart. Good luck.

Arrrrgh.

[petekoch]

I tried to put what must be typed into the cells in Bold-Face. However, I mistakenly put Cell P44 and P45 in Bold-Face. In both cases, the formulae begin with the equal sign.

All changes are, of course, to the Main worksheet.

[petekoch]

Apostraphe? I meant Apostrophe!

And it's faint of heart, not feint.

I must need an extra ration of hardtack.

Arrrrrgh.

[petekoch]

Originally Posted by Hobbes

The cash flow statement from MSN is a very dynamic page. There are 2 issues that I had. First, some companies have more categories than others...so the rows you are looking for move up and down. Second, if a company has less that 5 years of data the columns shift around.

I get the column number by matching to the year:
=IF(ISERROR(MATCH(2006,B3:F3,0)),0,MATCH(2006,B3:F 3,0))
=IF(ISERROR(MATCH(2005,B3:F3,0)),0,MATCH(2005,B3:F 3,0))
...etc.

Hey, what do you think of this? In my fix, in COL N, I've got the COL index. If, instead of numbers like 5,4,3,2,1, what if we derive the COL# from the above MATCH function? The whole problem is solved, n'est ce pas? The only downside is that the cells have to be changed when FY 2007 numbers get rolled out, not a biggy. Unfortunately, it will have to wait until at least tomorrow.

Comments? Critiques? Observations?

Arrrrgh.

[petekoch]

In order to test this last idea, it would help if I could recreate the problem. I put in PWE, which only has data beginning in 2004, but everything looked fine.

Hobbes, can you or anyone else provide a couple of stocks which experience the column problem?

[SeanieT]

Originally Posted by petekoch

Hey, what do you think of this? In my fix, in COL N, I've got the COL index. If, instead of numbers like 5,4,3,2,1, what if we derive the COL# from the above MATCH function? The whole problem is solved, n'est ce pas? The only downside is that the cells have to be changed when FY 2007 numbers get rolled out, not a biggy. Unfortunately, it will have to wait until at least tomorrow.

Comments? Critiques? Observations?

Arrrrgh.

Hey Pete,

If you have already made the changes to the Spreadsheet. Can I get a copy from you? Because trying to decipher your formulas through message traffic is a bitr difficult. I would like to see it in Excel form . Then maybe I will have an observation.