+ Reply to Thread
Results 1 to 7 of 7

VBA code/loop that copies and pastes data from a matrix to create historical time series

  1. #1
    Registered User
    Join Date
    11-07-2013
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    30

    VBA code/loop that copies and pastes data from a matrix to create historical time series

    Hello -

    I need to create a VBA loop that applies a weekly (7-day iteration) to a selected "end date." The goal is lag the end date by 1-week and copy and paste data from a matrix I created in order to generate a time series of signals, which I call ranks, until the start of the data set. For instance, the data set is currently based on a start date of 02/1/2004 and an end date of 11/12/2013, which should generate a time series of 468 points. The sheet is attached.

    Start and End date on the "risk" tab control the dates for the entire sheet. As such, the loop needs to offset the end date on that tab to begin the weekly iterations. The end date would be offset by 7 days, 14 days, 21 days and so on until the start date. The result is that each time the loop starts a new week rows 20 and 21 from the "ranking model" tab are copied and pasted into a new tab.

    Each iteration of the loop (weekly change) will copy and paste rows 20 (positioning rank) and 21 (currency) and create a historical time series of the rankings. The goal is to generate a historic time series of these rows by copying and pasting them into a seperate tab. The data feeds are APIs from Bloomberg so I imagaine it take a bit of time to run through each iteration and copy and paste the data. I am a VBA novice so any thoughts about how to configure this loop and create a time series of the aforementioned rows (data points) is greatly appreciated.

  2. #2
    Registered User
    Join Date
    11-07-2013
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: VBA code/loop that copies and pastes data from a matrix to create historical time seri

    matrix.jpg

    the file is too large but here is a screen shot of the time series I would like to create. The goal is to use the loop to get a weekly history of rows 20 (positioning rank) and 21 (currency) for my selected dates. I will provide screen shots of those pages as well.

  3. #3
    Registered User
    Join Date
    11-07-2013
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: VBA code/loop that copies and pastes data from a matrix to create historical time seri

    dates.jpg

    Here is a screen shot of the date loops. The loop would offset the end date (B2) by iterations of 7 days until reaching the start date (B1). Each weekly interation will then copy and paste the rows from the previous screen shot and dump the data into a new tab. As you can see from the screen shot there are 15 tabs of inputs that feed into the matrix, from risk to RR_1m. However, the start and end date for each date is based on the start and end date in the "risk" tab so B1 and B2 in risk should control the loop.

  4. #4
    Registered User
    Join Date
    11-07-2013
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: VBA code/loop that copies and pastes data from a matrix to create historical time seri

    Any thoughts? I think I have a workable code but cannot get the Bloomberg API links to refresh. Is there an application I can add to refresh the Bloomberg API links after each step of the loop is run. Here is the code:

    HTML Code: 

  5. #5
    Registered User
    Join Date
    11-07-2013
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: VBA code/loop that copies and pastes data from a matrix to create historical time seri

    I tried to delay the code for 20 seconds but that is too slow and still does not get the worksheets to refresh. Is there something I can add to the code to get the all the Bloomberg links in multiple worksheets to refresh after each step of the loop?

  6. #6
    Registered User
    Join Date
    11-07-2013
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: VBA code/loop that copies and pastes data from a matrix to create historical time seri

    10142013rankingscore1.jpgloop_results.jpg

    Here are the results of the loop. It should capture rows 21 but since the Bloomberg links do not refresh it does not capture the right data.

  7. #7
    Registered User
    Join Date
    11-07-2013
    Location
    NYC
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: VBA code/loop that copies and pastes data from a matrix to create historical time seri

    Sorry. The loop captures a historic time series of row 20.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro that copies data from one sheet and pastes it to another x columns to the right
    By Stevebarnes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-30-2013, 12:26 PM
  2. [SOLVED] Automating a macro that copies and pastes data and pastes 2 rows under the previous one.
    By thebank in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2012, 05:02 PM
  3. I have a script in where it copies a sheet's data, and pastes it, but over pastes...
    By Cyberpawz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-24-2012, 02:20 AM
  4. For loop that pastes data using calculated row # and advances +1 column each time.
    By rclark430 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-01-2011, 06:50 PM
  5. Replies: 0
    Last Post: 04-21-2010, 06:14 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1