stock quote help, VLOOKUP or what??

[Carl LaFong]

I need help with the finer points of retaining closing prices for a group of
mutual funds on a weekly and monthly basis. I have a large Excel 2007 file
containing 70 worksheets and the solution has to work well within this file.

Using data/get external data/from web, I have been able to download closing
prices to a new worksheet in the file. For this discussion, suppose A1 is
Fund X and A2 is 40.00, the closing price. I then need to populate the 40.00
price to various other worksheets in the file, at the end of every week and
month.

I know how to do that with a formula directly referencing the worksheet and
A2, and I have figured out how to use VLOOKUP to do much the same thing. So
far, so good.

But I can foresee problems when the day comes that row A in the download
worksheet no longer refers to Fund X. Perhaps it has been sold and I am no
longer tracking it. In that case, any previously entered formula referring
to cell A2 is going to give another value or generate an error. That's not
good because all my historical data will show errors or erroneous values as
well. Unfortunately, when the formula does its thing, the cell contains a
reference back to A2, rather than the 40.00 value per se. If A2 changes, the
displayed value in any cell containing a formula referencing A2 changes as
well. I want to be able to retain my historical weekly and monthly prices,
even though the download worksheet may have been significantly altered or
even deleted, for that matter.

My Excel skills are modest. Not a novice, but hardly an expert. I have never
used VLOOKUP until today, and have never used macros or anything slightly
exotic. I have always used ordinary formulas such as
=((weeklydata!N233/weeklydata!N232)-1)*100 and too much copy and pasting,
which I am trying to minimize.

How do I work around this?? I hope my explanation is lucid. If not, I will
try to clarify.

 

[MyVeryOwnSelf]

I need help with the finer points of retaining closing prices for a

group of mutual funds on a weekly and monthly basis. I have a large
Excel 2007 file containing 70 worksheets and the solution has to work
well within this file.

Using data/get external data/from web, I have been able to download
closing prices to a new worksheet in the file. For this discussion,
suppose A1 is Fund X and A2 is 40.00, the closing price. I then need
to populate the 40.00 price to various other worksheets in the file,
at the end of every week and month.

I know how to do that with a formula directly referencing the
worksheet and A2, and I have figured out how to use VLOOKUP to do much
the same thing. So far, so good.

But I can foresee problems when the day comes that row A in the
download worksheet no longer refers to Fund X. Perhaps it has been
sold and I am no longer tracking it. In that case, any previously
entered formula referring to cell A2 is going to give another value or
generate an error. That's not good because all my historical data will
show errors or erroneous values as well. Unfortunately, when the
formula does its thing, the cell contains a reference back to A2,
rather than the 40.00 value per se. If A2 changes, the displayed value
in any cell containing a formula referencing A2 changes as well. I
want to be able to retain my historical weekly and monthly prices,
even though the download worksheet may have been significantly altered
or even deleted, for that matter.

My Excel skills are modest. Not a novice, but hardly an expert. I have
never used VLOOKUP until today, and have never used macros or anything
slightly exotic. I have always used ordinary formulas such as
=((weeklydata!N233/weeklydata!N232)-1)*100 and too much copy and
pasting, which I am trying to minimize.

How do I work around this?? I hope my explanation is lucid. If not, I
will try to clarify.

I have a similar Excel file to track portfolio values and here's what I do.
It isn't elegant, but it works for me. Maybe some of the ideas will be
useful.

I keep monthly sheets for the current year, named "Jan", "Feb", etc.;
twelve sheets in all. On each sheet, column A is the ticket symbol and
column C is the corresponding share price. I use winstock
(http://winstocksw.com/) to get the share prices.

Next, I have three sheets: "Share Price", "Num Shares", and "Value". In
each of these, D1:O1 contains the last dates of the twelve months of the
current year.

The rows of these three sheets line up with each other. For example row 4
of "Share Price" is the price of a security for the month identified by the
column, "Num Shares" is how many shares of that security are in the
portfolio that month, and "Value" is the product. The way my sheets are
arranged, D4 is the first month for the first security.

Here's how I address the issues you raise.

In the "Num Shares" twelve-column array, type in the number of shares for
that security for that month. Before purchase or after sale, it's zero or
empty or maybe a textual comment.

In the "Share Price" sheet, column A is the ticker symbol, starting with
A4. In D4 put
=IF(N('Num Shares'!D4)>0,
VLOOKUP($A4,INDIRECT(TEXT(D$1, "Mmm")&"!A:C"),3,FALSE), "")
Copy D4 and paste it into the entire array of twelve columns and as many
rows as needed. The INDIRECT chooses the monthly sheet based on the date in
row 1. The VLOOKUP gets the share price for that security for that month.
The IF skips the lookup if the number of shares is zero or non-numeric.
This way, the monthly sheets need only have securities that are in the
portfolio that month.

In the "Value" sheet, put in D4
=IF(N('Num Shares'!D4)>0, 'Share Price'!D4*'Num Shares'!D4, "")
Copy D4 and paste it into the entire array of twelve columns and as many
rows as needed.

 

[krcowen]

Carl

I am not totally clear about what you are trying to do, but, assuming
that you are propogating the $40 closing price to other worksheets in
cells immediately to the right of the fund name, and the funds are
always going to be listed in column A of sheet1 and the closing prices
are going to be in column B of sheet1, then vlookup would be a good
choice, something like

=vlookup(cell to the left, sheet1!A:B,2,false) would give you the the
value in column B that corresponds with the fund name, if there is a
matching fund name.

But, that is not really consistent with what you wrote. From what you
posted, it seems like the fund names may be in row 1 and the closing
prices in row 2. In that case you would probably need a hlookup,
something like

=hlookup(cell to the left, sheet1!1:2,2,false) would give you the
closing price in row 2 that corresponds to the fund name in row, if
there is a matching fund name.

If however, your data has fund 1 in A1, its closing price in A2, then
fund 2 in A3 and its closing price in A4, then you are screwed.

Good luck.

Ken

 

[Carl LaFong]

Ken:

There was a typo in my original post.

The 4th paragraph should begin with "But I can foresee problems when the day
comes that column A in the download worksheet no longer refers to Fund X". I
had written row A.

My download tab has fund ticker symbols in column A and closing prices in
the immediately adjacent column B. I need to periodically propagate the
column B prices to various other locations in the same Excel file-typically
to a "weekly" or "monthly" or "quarterly" performance tab. I download prices
daily, but retain only weekly/monthly/quarterly/annual prices.

Suppose today's download says A1 is Fund X and A2 is 40.00 and I have price
and performance data dating back several months or years for Fund X.

I know how to propagate A2 via a direct formula referencing A2 and I
discovered yesterday how to do that through VLOOKUP. That is not the
problem.

Suppose I sell Fund X and therefore no longer continue to download its
price. Cell A2 in the downloads tab may then contain the price for a
replacement Fund Y. However, I DON'T want to lose the historical info dating
back years on the sold Fund X. Since A2 now refers to Fund Y, any formula
referring to A2 will be based on Fund Y, not Fund X. Historical info from
prior months and years regarding Fund X is overwritten, even though I just
sold it. I need to retain the historical info for any fund I may have owned
in the past, REGARDLESS of what fund prices I am currently tracking and
downloading.

Any ideas?? Am I missing something?

MyVeryOwnSelf:

I am certainly willing to look at your solution. Before spending time
developing it, can you give me a layman's description of specifically how it
avoids the overwrite problem? As I understand it, you have 15 tabs-January
through December, plus Share Price, Num Shares, and Value, and you make new
tabs each January. How do you then save the info for prior years, assuming
you have solved my overwrite problem??

I hope this clarifies things a bit.

 

[MyVeryOwnSelf]

MyVeryOwnSelf:

I am certainly willing to look at your solution. Before spending time
developing it, can you give me a layman's description of specifically
how it avoids the overwrite problem? As I understand it, you have 15
tabs-January through December, plus Share Price, Num Shares, and
Value, and you make new tabs each January. How do you then save the
info for prior years, assuming you have solved my overwrite problem??

Since the "Share Price" tab uses VLOOKUP, the order of rows in the monthly
tabs doesn't matter. It doesn't matter if stocks are added and deleted from
month to month. What matters is: if a particular stock is in the portfolio
for a particular month, it has a row _somewhere_ in that month's monthly
tab.

When I add a new stock to the portfolio, I add a row to the three tabs
(Share Price, Num Shares, Value) all at once. Among these three tabs, the
order of the rows do match, and there hasn't been any problem about it.

At the beginning of the year, I start a new workbook by copying the old one
and deleting from the three tabs the rows for the stocks no longer in the
portfolio. Lots of constant values are deleted (numbers of shares, all the
contents of the monthly tabs) but the formulas carry over.

I keep the workbooks from prior years.

What I don't do is track trends for individual stocks over many years. I
imagine that's possible using links between workbooks, but I haven't worked
out the details. Since that kind of info is available generically from lots
of web sites, I haven't felt the need. (I do track the portfolio as a whole
over the years; just totals.)

 

[Carl LaFong]

MyVeryOwnSelf:

I have set up a dummy file to replicate your method, using your directions
as best I can. I have been hacking at it for 2 or 3 hours.

I am trying to get a price to come up in Share Price cell D4, which should
represent the January closing price for the first security in my list in the
Jan tab. I am getting a REF error, not surprisingly.

Trying to parse the formula in D4, my guess is that I may have the date
reference in Share Price D1 entered incorrectly. I have tried 1/31, Jan 31,
31, 1/31/07, etc with no luck. The month tabs are named Jan, Feb, Mar, Apr,
May, Jun, etc.

I assume Mmm in the formula refers to the 3 letter month designation, such
as Jan, Feb, and I assume the D$1 is what forces INDIRECT to choose Jan
rather than Feb.

So, what exactly do you have in Share Price D1 and how is row 1 formatted in
the Share Price, Num Shares, and Value tabs?

 

[MyVeryOwnSelf]

I assume Mmm in the formula refers to the 3 letter month designation,

such as Jan, Feb, and I assume the D$1 is what forces INDIRECT to
choose Jan rather than Feb.

Right. It takes the "month" component of the date in row 1, expresses it
as the three-character name and uses those the characters to identify the
tab.

If number of shares > 0 and the stock isn't in the Jan tab, I'd expect
#N/A, not #REF.

So, what exactly do you have in Share Price D1 and how is row 1
formatted in the Share Price, Num Shares, and Value tabs?

I have formulas to compute the last day of each month, and format those
cells as dates:
Format > Cells > Number > Date

My formulas are more elaborate than necessary, something like this:
A1: 2007 [current year as a constant number]
D1: =DATE($A$1,2,0) [last day of January; i.e., zeroth day of February]
E1: =DATE($A$1,MONTH(D1)+2,0) [last day of February]
Copy E1; select F1:O1; and paste.
I do this on one of the three sheets; on the others, row 1 refers to that
one for end-of-month dates.


If there's still a problem, try building up the D4 formula one part at a
time and see where things start to go wrong.

=TEXT(D$1, "Mmm") [should be Jan]
=TEXT(D$1, "Mmm")&"!A:C" [should be Jan!A:C]
='Num Shares'!D4 [should be the number of shares]
='Num Shares'!D4>0 [should be TRUE]
=N('Num Shares'!D4)>0 [should be TRUE]
=VLOOKUP($A4,INDIRECT(TEXT(D$1, "Mmm")&"!A:C"),3,FALSE) [should be the
share price]

Combine these two lines for the complete formula:
=IF(N('Num Shares'!D4)>0,
VLOOKUP($A4,INDIRECT(TEXT(D$1, "Mmm")&"!A:C"),3,FALSE), "")

 

[Carl LaFong]

MyVeryOwnSelf:


Thanks to you, I have this thing working fairly well now.

I put in some mock entries in the Jan tab to get the formula working.

However, my download source for live data insists on putting the ticker
symbol in Column B of the Jan tab, rather than Column A, which of course
generates an error back on the Share Price tab.

The Excel help file is a bit cryptic. Is it correct that the formula cannot
be modified to force a search in Column B of the Jan tab and therefore find
the ticker symbol? Hiding Column A is no help either.

Even if that is true, I am guessing I can find another download source that
will drop the tickers in column A. Mebbe I should look at winstock as you
mentioned.

Lastly, I see that the =TEXT(D$1, "Mmm")&"!A:C" portion of the formula does
in fact generate Jan!A:C, but I don't understand why you need it. The
overall formula seems to generate errors if that portion is omitted.

I assume Mmm in the formula refers to the 3 letter month designation,
such as Jan, Feb, and I assume the D$1 is what forces INDIRECT to
choose Jan rather than Feb.

Right. It takes the "month" component of the date in row 1, expresses it
as the three-character name and uses those the characters to identify the
tab.

If number of shares > 0 and the stock isn't in the Jan tab, I'd expect
#N/A, not #REF.

So, what exactly do you have in Share Price D1 and how is row 1
formatted in the Share Price, Num Shares, and Value tabs?

I have formulas to compute the last day of each month, and format those
cells as dates:
Format > Cells > Number > Date

My formulas are more elaborate than necessary, something like this:
A1: 2007 [current year as a constant number]
D1: =DATE($A$1,2,0) [last day of January; i.e., zeroth day of February]
E1: =DATE($A$1,MONTH(D1)+2,0) [last day of February]
Copy E1; select F1:O1; and paste.
I do this on one of the three sheets; on the others, row 1 refers to that
one for end-of-month dates.


If there's still a problem, try building up the D4 formula one part at a
time and see where things start to go wrong.

=TEXT(D$1, "Mmm") [should be Jan]
=TEXT(D$1, "Mmm")&"!A:C" [should be Jan!A:C]
='Num Shares'!D4 [should be the number of shares]
='Num Shares'!D4>0 [should be TRUE]
=N('Num Shares'!D4)>0 [should be TRUE]
=VLOOKUP($A4,INDIRECT(TEXT(D$1, "Mmm")&"!A:C"),3,FALSE) [should be the
share price]

Combine these two lines for the complete formula:
=IF(N('Num Shares'!D4)>0,
VLOOKUP($A4,INDIRECT(TEXT(D$1, "Mmm")&"!A:C"),3,FALSE), "")

 

[MyVeryOwnSelf]

However, my download source for live data insists on putting the

ticker symbol in Column B of the Jan tab, rather than Column A, which
of course generates an error back on the Share Price tab.

The Excel help file is a bit cryptic. Is it correct that the formula
cannot be modified to force a search in Column B of the Jan tab and
therefore find the ticker symbol? Hiding Column A is no help either.

Here's how VLOOKUP works in this case.

As you noticed, the INDIRECT reduces the formula for January to

VLOOKUP($A4,Jan!A:C"),3,FALSE)

This means

- look in the Jan tab

- treat columns A:C as a lookup table, with the first column
of the table having the lookup value.

- Look in that first column for the value in $A4 (ticker symbol)

- return the looked-up value that's in the third column (C here)
of the table (that's the 3 in the formula); in my case that's
the share price

- FALSE means an exact match is required.

So if in your case the ticker symbol is in Jan!B and the share price is
in Jan!C, it should work to replace A:C with B:C and 3 with 2.

Even if that is true, I am guessing I can find another download source
that will drop the tickers in column A. Mebbe I should look at
winstock as you mentioned.

Your choice. I've used winstock for several years and like it. Just a
personal preference. There are also web site's that'll provide the share
prices for free.

Lastly, I see that the =TEXT(D$1, "Mmm")&"!A:C" portion of the formula
does in fact generate Jan!A:C, but I don't understand why you need it.
The overall formula seems to generate errors if that portion is
omitted.

See above. I use INDIRECT in the formula so I can copy any cell of the
12-column array and paste it anywhere else in the array. This is helpful
when adding stocks.

 

[Carl LaFong]

Got It!!!

I had tried B:C rather than A:C, but did not replace 3 with 2, figuring that
the formula would count 3 columns beginning with A and find Column C in the
Jan tab, when in fact it counts 3 columns from the starting point of the
lookup table (Column B), which would be the empty Column D. The 2 forces it
to count to the C column in my case.

I may well adopt this and sincerely thank you for your patience.