Excel Tip: Extract Last Name

=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

Comment: HT Link

Yahoo: EZ download of stock data

Downloading Yahoo data

Comment: Much cool stuff but for a simple download of stock symbol and last closing price put this string in URL browser window: 

http://download.finance.yahoo.com/d/quotes.csv?s=ABBV+ABT+AMLP+CA+CAG+CLX+COP+CSCO+ED+GIS+GS+JNJ+KMB+KO+KR+KRFT+MRK+MSFT+PG+PULB+STT+STX+SYY+TGT+WM&f=sp

Excel VLOOKUP example

A real life example:

• I have multiple spreadsheets that deal with stocks: Stocks I am analyzing, stocks I may purchase or sell, et cetera
• I had been using named cells for updating prices. For each spreadsheet I had a extra table called "price table" that I used to maintain these prices. This works but: If I sorted the table on stock symbol the named cell with the price data did not carry with the sort. AND because I have multiple spreadsheets that deal with stocks, I had to maintain an extra table for prices on each workbook. What a pain!
• I had the day off yesterday and began to research using VLOOKUP. I read some tutorials in the afternoon and then last night as we were watching TV, I worked with for about an hour. 
• Summary: Very easy!
• I created a new worksheet called "Stock_prices.xlxs"
• And then used the VLOOKUP function in my stock spreadsheets to populate the price, dividend, and PE data. 
• An image of the VLOOKUP example is above (with results). And the stock prices table below.

Cell B2 (price for ED) Explained

VLOOKUP has 4 arguments. The first three are essential:

• A2: Use the value in A2 as the search argument. The Value of A2 is "ED" (Con Ed)
• [Stock_Prices.xlsx]Sheet1!$2:$1001:
  • [Stock_Prices.xlsx]: Use external table Stock_Prices.xlsx (comment: has to be sorted (in this case on stock symbol)
  • Sheet1!$2:$1001: From named worksheet "Sheet1" .... rows 2 to 1001
• 2: Data in the 2nd column. In this case the price of the stock

Get The Day Of The Week From A Date

HT: www.theexceladdict.com

Have you ever needed in Excel to populate the day of week in a separate column? Two easy ways:

Method 1:

=TEXT(WEEKDAY(A2),"dddd")

Method 2:

=CHOOSE(WEEKDAY(A2),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")