0

I have 2 year daily closing prices for Bitcoin, from 1st August 2016 to 31st July 2018. Bitcoin can be traded 7 days a week so this data includes the dates of weekends:

enter image description here

I want to add more data sets, including closing prices for platinum, and the NASDAQ index, and these data sets only contain dates for 5 days a week, skipping weekends since no trading occurs then.

I have a column of the full week dates over my two year period, but I want to include in a second column, the corresponding closing prices of platinum and NASDAQ, but the data gets muddled since it skips weekends. E.g. the 29th July was last Sunday, but if I copied over the data from NASDAQ, that day would correspond to Friday since it doesn't have Saturday or Sunday in its dates.

In Excel, how can I insert these weekend dates, whilst keeping the closing prices in line with the correct corresponding dates for NASDAQ and platinum? I want the closing prices on the inserted weekends to be 0.

Improve this question
2
  • 1
    VLOOKUP may be of use to you
    – cybernetic.nomad
    Commented Aug 1, 2018 at 18:43
  • Thank you @cybernetic.nomad ! Could you please explain, in relation to this particular situation how I might use this function?
    – D. McGuire
    Commented Aug 1, 2018 at 18:47

1 Answer 1

Reset to default
0

Use the following formula:

=VLOOKUP(A25,AA$2:AB$1000,2)

Where A25 contains the date (per your screen cap) and AA$2:AB:1000 is the range containing the NASDAQ index, 2 is the number of the column in the range specified (AA$2:AB$1000 in the example)

Improve this answer
5
  • I'm having some issues still. My friend nor I don't know why we can't get it to work but this is the command I'm using: =LOOKUP(B5,BTC!$A$29:$A$758,BTC!$F$29:$F$758) Where B5 is the first date for NASDAQ entries, BTC! is the sheet with Bitcoin entries, $A$29:$A$758 is the column with the dates for bitcoin, on the bitcoin sheet, and $F$29:$F$:758 is the closing prices for bitcoin, but all that happens is I get repeated entries when I drag down the formula. Any ideas why this is happening? thanks
    – D. McGuire
    Commented Aug 1, 2018 at 20:06
  • Change it to: ` =LOOKUP(B5,BTC!$A$29:$F$758,6)`
    – cybernetic.nomad
    Commented Aug 1, 2018 at 20:17
  • Still nothing! That just comes up with #N/A
    – D. McGuire
    Commented Aug 1, 2018 at 20:25
  • Missed the typo, it's VLOOKUP, not LOOKUP
    – cybernetic.nomad
    Commented Aug 1, 2018 at 20:56
  • VLOOKUP had the same problem, but actually we found out it's a silly reason it didn't work! Excel demands that you have all your data in oldest to newest order, so I had to sort all my data, then use: =LOOKUP(B5,BTC!$A$29:$A$758,BTC!$F$29:$F$758) Once I'd done that, it was all working! Thank you for taking your time to help me, much appreciated!
    – D. McGuire
    Commented Aug 1, 2018 at 21:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .