Can R do the equivalent of an HLOOKUP nested within a VLOOKUP

0 votes

I'm using R Studio to attempt—ineffectively—to perform the Excel equivalent of an HLOOKUP nested within a VLOOKUP.

The scenario is as follows.

I've got two tables. Historical stock prices are shown in Table 1, where each row corresponds to a specific date and each column to a ticker name. The closing stock price for each ticker on each date is shown in Table 1.

Let's say Table 1 appears as follows:

 |----------------------------|
 |   Date   |MSFT | AMZN |EPD | 
 |----------------------------|
 | 6/1/2020 | 196 | 2600 | 19 |
 | 5/1/2020 | 186 | 2200 | 20 |
 | 4/1/2020 | 176 | 2000 | 15 |
 | 3/1/2020 | 166 | 1800 | 14 |
 | 2/1/2020 | 170 | 2200 | 18 |
 | 1/1/2020 | 180 | 2300 | 17 |
 |----------------------------|

A set of ticker symbols, two dates, and placeholders for the stock price on each date are shown in Table 2. Each of Date1 and Date2 corresponds to a date in Table 1, and Date1 is always earlier than Date2. In Table 2, you'll see that Date1 and Date2 are distinct for each row.

In Table 2, I want to insert the relevant PriceOnDate1 and PriceOnDate2 data in a manner similar to how Excel's VLOOKUP and HLOOKUP functions work. (I am no longer able to use Excel for this because the file is too large for Excel to handle.) Then, using a formula like this, I can determine the return for each row: Date2 divided by Date1

Assume I want Table 2 to look like this, but I am unable to pull in the pricing data for PriceOnDate1 and PriceOnDate2:

|-----------------------------------------------------------|
| Ticker | Date1    | Date2    |PriceOnDate1 |PriceOnDate2  |
|-----------------------------------------------------------|
| MSFT   | 1/1/2020 | 4/1/2020 | _________   | ________     | 
| MSFT   | 2/1/2020 | 6/1/2020 | _________   | ________     |   
| AMZN   | 5/1/2020 | 6/1/2020 | _________   | ________     |
| EPD    | 1/1/2020 | 3/1/2020 | _________   | ________     |   
| EPD    | 1/1/2020 | 4/1/2020 | _________   | ________     |
|-----------------------------------------------------------|

My question is whether there is a way to use R to pull into Table 2 the closing price data from Table 1 for each Date1 and Date2 in each row of Table 2. For instance, in the first row of Table 2, ideally, the R code would pull in 180 for PriceOnDate1 and 176 for PriceOnDate2.

Nov 11 in Others by Kithuzzz
• 20,660 points
35 views

1 answer to this question.

0 votes

When working in a program like R, you must adopt a slightly different perspective on the data. Your Table 1's lengthy format makes it perhaps the easiest to work with. The desired values can then be pulled by simply joining the Ticker and Date together.

Data:

table_1 <- data.frame(Date = c("6/1/2020", "5/1/2020", "4/1/2020", "3/1/2020", 
                               "2/1/2020", "1/1/2020"),
                      MSFT = c(196, 186, 176, 166, 170, 180),
                      AMZN = c(2600, 2200, 2000, 1800, 2200, 2300),
                      EPD = c(19, 20, 15, 14, 18, 17))

# only created part of Table 2
table_2 <- data.frame(Ticker = c("MSFT", "AMZN"),
                      Date1 = c("1/1/2020", "5/1/2020"),
                      Date2 = c("4/1/2020", "6/1/2020"))

Solution:

The tidyverse approach is pretty easy here.

library(dplyr)
library(tidyr)

First, pivot Table 1 to be longer.

table_1_long <- table_1 %>% 
  pivot_longer(-Date, names_to = "Ticker", values_to = "Price")

Then join in the prices that you want by matching the Date and Ticker.

table_2 %>% 
  left_join(table_1_long, by = c(Date1 = "Date", "Ticker")) %>% 
  left_join(table_1_long, by = c(Date2 = "Date", "Ticker")) %>% 
  rename(PriceOnDate1 = Price.x,
         PriceOnDate2 = Price.y)

#   Ticker    Date1    Date2 PriceOnDate1 PriceOnDate2
# 1   MSFT 1/1/2020 4/1/2020          180          176
# 2   AMZN 5/1/2020 6/1/2020         2200         2600

answered Nov 11 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers

how to list the contents of a asset into an event

May 29, 2019 in Others by anonymous
247 views
0 votes
1 answer
0 votes
0 answers
0 votes
0 answers

can the font type of an Edittext,Radio Button and CheckBox be changed in Android

TextView text = (TextView) layout.findViewById(R.id.text); text.setText(msg); Typeface font = ...READ MORE

Jun 22 in Others by nisha
• 2,190 points
89 views
0 votes
1 answer

Download multiple excel files linked through urls in R

Try something along the lines of: for (i ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
72 views
0 votes
1 answer

Excel stock and sales data management

you must attach the event handler each ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
43 views
0 votes
1 answer

Using Excel VLOOKUP() function across two sheets

The syntax for VLOOKUP is VLOOKUP(Lookup_Value,Table Array,Col_index_num,Range_lookup) OR, to start in ...READ MORE

answered Sep 30 in Others by narikkadan
• 37,660 points
61 views
0 votes
1 answer

Way to overcome Excel Vlookup function limit of 256 characters

If you are using VLOOKUP like this: =VLOOKUP(A2,D2:Z10,3,FALSE) i.e. ...READ MORE

answered Sep 30 in Others by narikkadan
• 37,660 points
119 views
0 votes
1 answer
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
57 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP