Forums  > Software  > excel q  
Page 1 of 1
Display using:  


Total Posts: 11
Joined: Aug 2016
Posted: 2016-08-08 01:02
Good day all,

I am currently a financial engineering grad student and I’ve recently discovered this great forum. I’m having a small problem that maybe one of you guys with more expertise than me could help me with, that’ll be much appreciated.

In Excel, I am trying to find a way to have each fx pairs data on the same line for a given period of time. The reason for this is that I’m using these data in formulas which results are showed in a graph.
The problem is that it seems like there is discrepancies between pairs in the data given by my provider. In example, sometimes there is a period where data is missing for usdjpy only, which then breaks the chronological order of the data in the sheet, making the results of the formulas useless.
What I do is I usually just delete all data of the specific period missing on a single pair from each pairs in the file, as if it never existed. With daily data, there can be maybe 5 periods that I need to adjust like that, no big deal. But with hourly or less, the amount of time needed to do this is just crazy.

Perhaps maybe there is a simpler way of resolving this.

Thanks for your help


Total Posts: 259
Joined: Oct 2004
Posted: 2016-08-08 12:28
you need to resample yourself (i'd say move to python because it's easier). you can build a time index and vlookup the relevant data for each pair, and then apply an error correction (take last data point if non existing for example, or the average, etc...).
Excel is not very friendly with resampling of data, but it's the easiest way to control most aspect of it.

Et meme si ce n'est pas vrai, il faut croire en l'histoire ancienne


Total Posts: 11
Joined: Aug 2016
Posted: 2016-08-08 17:40
Thanks for the tip. Not really an expert in programming, I only have some basic knowledge in matlab, c++ and sql, but I will look into python.


Total Posts: 11
Joined: Aug 2016
Posted: 2016-08-08 18:03
Here is some screenshot to give a clearer idea of what I'm trying to do.
1st pic: Each pairs data are in the same chronological order, all good.
2nd pic: Data missing for 2 periods on gbpaud, order get messed up.

I thought about including a logical test in the period column, like =if(Q1+V1+AA1=Q1*3;Q1;Q2) ... But that would only work if there is a maximum of one period missing!


Total Posts: 461
Joined: Apr 2005
Posted: 2016-08-08 18:40
As tbretagn said - this would be far easier to do in Python
If you're really wedded / stuck to Excel - you can write you own VLookup style function in VBA which deals with this - but really - Python or similar is the way (or R - but personally a Python guy...)
If you can do some basic programming in other languages - you'll pick it up in no time..


Total Posts: 18
Joined: Mar 2013
Posted: 2016-08-08 23:33
cf_mstr, if you don't want to leave excel, just create a list of time stamps that show up three times (using the example you posted) and then vlookup for each of them.
Not pretty, not fast, but gets the job done.


Total Posts: 11
Joined: Aug 2016
Posted: 2016-08-09 17:14
Thanks all for the help, time to move to python


Total Posts: 11
Joined: Aug 2016
Posted: 2018-06-10 05:25
Giving a solution I've found a while ago to the initial problem, if one's has to stay in excel for some reasons. Hope this can help someone that also had this issue

1. Gather your data for different assets into the same columns
2. Identify which asset the data belongs on the same line
3. Sort the data in chronological order
4. Enter the logical formula shown in the screenshot below. That was for 3 assets in total, now the more or less assets you have, you will have to adjust the formula accordingly
5. Now if there is data for a specific period for all assets, the result will be true. Paste data the columns with that formula
6. Filter your columns,get only the data where result is true
7. Voilà, you can mix different assets in formulas/graphs in excel without having messed up results from missing data for a specific asset

Previous Thread :: Next Thread 
Page 1 of 1