I've attached a workbook which shows two sets of data. One is the historical prices of the S&P 500 and the other is the same, for Bitcoin. Since Bitcoin's prices change daily, but the stock market is only open on business days, I can't run a direct, day:day correlation.
How could I "match" the dates, where we ignore the weekend/holiday bitcoin prices? (then, once matched, check the correlation between the two). I assume Vlookup is involved.
Thanks
i pulled list of US holidays off some website - only 2018
anyways its every state so you need to purge as required and do the same for 2015 to 2017
then i used this for the formula
which is
Formula:
Please Login or Register to view this content.
first determine if weekend
if weekend gives "Weekend
else...checks if holiday
if holiday returns "holiday"
else looks up the S&P equivalent for that day...
now the correlation formula in formula H isn't what you probably want but its just to finish the example off
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
I calculate there are 15 dates missing from the Bitcoin history.How do you want to handle those?Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 05-14-2018 at 11:37 PM.
Dave
Thanks! Is there a way to use vlookup or match in a way that creates a new set of price histories, which only contains data if both
column A and Column D have matching dates?
This way, either column could have "missing" dates and we would wind up with two new datasets with identical histories.
(I'm on a little notepad at the moment so I can't visualize the workbook. But I think the dates for S&P history are in Column A
and the dates for Bitcoin are in Column D)
ARRAY formula in G3 for common date
IN H3 for S&P
Please Login or Register to view this content.
In I3 for BitcoinPlease Login or Register to view this content.
Drag down all formulas.Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Thank all of you for helping out an old man! I'll close out this thread now.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks