In order to create some sort of basic framework for everything we are planning on doing in a research project, we would need to get historic intraday data about share prices at certain points in time. Lets say there is t = 12:05:00. We then would need the prices at e.g. t-30m, t-15m, t-5m, t-1m, t, t+1m, t+5m, t+15m, t+30m, t+120m.

I am currently trying to figure out if there is a way to achieve this in Excel by using only a Bloomberg formula that imports the price for t into a single excel cell for each point in time and then importing the other prices by changing the time in the formula according to the time difference I mentioned above. Given information is the ISIN (I figured out how to use it in formulas already), date and the time.

So far all the data I am able to import seems to be interday no matter if I add or change a point in time to the date in the formula.

Is there a very simple way of achieving what we are in need of that I have not discovered yet?

Thank you very much in advance!


EDIT: I already discovered the possibility to create a table of ticks, however those are in predefined intervals that are not matching the intervals I need and also there would be an overwhelming amount of tables because of the large number of securities I am planning to analyze.

  • $\begingroup$ Why not retrieve 1 minute data over the whole interval and then use vlookup formulae to get the datapoints you need? $\endgroup$ – assylias Mar 19 '19 at 13:16
  • 1
    $\begingroup$ I'm voting to close this question as off-topic because it about using Excel for data filtering / lookup and not quant finance. $\endgroup$ – LocalVolatility Mar 19 '19 at 15:07

Browse other questions tagged or ask your own question.