Find most recent stock quotes for a specific date

Question

I have a table in Google spreadsheet like this:

Stock                    Date          Price

DNB Nordic Technology    2016-07-12    968,7449
DNB Nordic Technology    2016-07-13    970,0482
DNB Nordic Technology    2016-07-14    970,0500
Elementa                 2016-03-30   1167,0704
Elementa                 2016-04-30   1175,8091
Elementa                 2016-05-31   1184,1240
Elementa                 2016-06-30   1196,5268
Evli Emerging Frontier B 2016-07-12   1164,6900
Evli Emerging Frontier B 2016-07-14   1171,3400

What I need is a table with only the most recent price for each stock for a specific date ex 2016-07-13

DNB Nordic Technology       2016-07-13  970,0500
Elementa                    2016-06-30 1196,5268
Evli Emerging Frontier B    2016-07-12 1166,9200

I want to use a query so I can use the result/table and run a single query using ARRAYFORMULA(sum... (quantity * price)) to get total value for my portfolio.

pls help :)

This query will result in latest date but no prices :(

QUERY('stock'!$A:$C;"select A, max(B) where A<>'' group by A label A '', max(B) ''")

Are you pulling in the data dynamically or is it a static table? — Aurielle Perlmann
Hi, this query is for calculate historical values so the table is semi static (I update it once a weak). But i need a query to calculate value for many dates and several portfolios so i still need a query. This is for calculate NAV for each portfolio. — Per-Åke Franklind
Sure, here is a sheet. Query in F2 will result in a table with name and last date, but not the price docs.google.com/spreadsheets/d/… — Per-Åke Franklind

Aurielle Perlmann Aurielle Perlmann · Accepted Answer · 2016-07-16T10:43:27

Here you go:

=ARRAYFORMULA(IF(ISTEXT(UNIQUE(A2:A)),VLOOKUP(UNIQUE(A2:A),SORT(A2:C,1,true,2,false),{1,2,3},false),))

What I did here was first give it a condition to only run on the rows that have a potential value, so it doesnt stall the sheet - then I sort, first by name (ascending), then by date (descending) so that we only need the first row of each name, date pair, then I return all the indexes

Here is the updated format for your locale:

=ARRAYFORMULA(IF(ISTEXT(UNIQUE(A2:A));VLOOKUP(UNIQUE(A2:A);SORT(A2:C;1;true;2;false);{1\2\3};false);))

IF YOU NEED THE DATE PARAMATER ALSO HERE IS THAT MODIFICATION:

 =ARRAYFORMULA(IF(ISTEXT(UNIQUE(FILTER(A:A;B:B<=D1)));VLOOKUP(UNIQUE(FILTER(A:A;B:B<=D1));SORT(FILTER(A:C;B:B<=D1);1;true;2;false);{1\2\3};false);))

All I did was add a filter function over the array - also note this formula doesnt need to use query at all

Find most recent stock quotes for a specific date

2 Answers