Tuesday, August 9, 2011

yogi_Consolidate Data Form Different Columns By Matching Rows In Two Different Sheets

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
Heooo said:
How can I compare ragged financial data?
I am trying to compare Vanguard VTI against another product here [1].
1. The problem is that [1] lacks valuations during some days, diverting results a lot.
2. And one problem more the Google Docs returns days in the format "%D/%M/%Y 16:00" while [1] returns them in the format "%D.%M.%Y".
I got the Vanguard valuations with
=GoogleFinance("VTI", "price", "29/12/2006", "8/8/2011", "daily")
that returns information in the form:
Date Close
29/12/2006 16:00:00 70.105
03/01/2007 16:00:00 69.975
04/01/2007 16:00:00 70.115
05/01/2007 16:00:00 69.56
08/01/2007 16:00:00 69.815
09/01/2007 16:00:00 69.815
10/01/2007 16:00:00 69.97
to a ragged local data in the form:
Date Close EU
29.12.2006 10
02.01.2007 9.928
04.01.2007 9.991
05.01.2007 10.042
09.01.2007 10.041
10.01.2007 10.07
I need to get the different day -format matching working before I can get the filter-match -function working. Does Google Docs can return the day in my specified format such as "%D.%M.%Y" so I do not need to do awkward parsing here?
As you will notice the date format used for the problem in this post is in  dd.mm.yyyy style
following is one way where I have primarily used the MATCH function and the VLOOKUP function