Macro To Force Refresh Bloomberg Data
Feb 8, 2007
I have a workbook that pulls in index data from Bloomberg, most data is updated realtime, but one index we track is only updated late at night. I want to write a macro that will refresh all of the data in the worksheet and then save a snapshot of the workbook. I have all the code for saving the snapshot, but getting the data for the one index to refresh is proving difficult. None of the standard ways I know to refresh external data (both Excel commands via tools menu, and Bloomberg specific refresh commands) seem to work on these cells. The only way so far I've found to get these cells to pull in current data is to delete them and rewrite the formulas.
suggestion for a Macro that would force a refresh of this data?
View 7 Replies
ADVERTISEMENT
Jun 24, 2014
I attempted doing this by recording the macro but the macro doesn't read me clicking onto Bloomberg Refresh Data Button. How can I do this using VBA?
View 1 Replies
View Related
Dec 10, 2009
Firstly you don't need to know anything about Bloomberg to answer this query, only that Bloomberg has various functions that take time to update.
The code Application.Run "RefreshEntireWorkbook" will update these functions but Application.OnTime (Now + TimeValue("00:00:25")), "RunList" is required to allow them time to get the data from the feed. However this only works if the refresh and wait commands are in a seperate function. So in simplified terms it must go like this in vba:
View 2 Replies
View Related
Jun 18, 2012
Excel 2010. I have a field that is color formatted based on the value of another field, which is calculated.
The conditional formatting does not refresh when the value is recalculated, but when I go away from the tab and come back, it works fine.
Any simple VB code to refresh the conditional formatting upon a workbook change?
View 1 Replies
View Related
Aug 21, 2014
i have the following macro that loops through 100 tickers in sheet 1, gets the trades for each, pushes each ticker's trades to sheet 2 to create a list of all trades. The problem is that I just get a #N/A requesting data for each ticker. This seems to be a wider problem with fetching external data from Bloomberg. However, I cannot seem to get a fix for it.
Sub Macro()
On Error Resume Next
Sheet2.Range("B3:E65536").ClearContents
[code]....
View 1 Replies
View Related
Dec 3, 2008
I hope there's someone who's familiar with Bloomberg in this forum.
My problem is, I have a template which conducts checks with data extracted a Bloomberg Terminal. I want to ensure that all data has FULLY loaded before I execute the checks.
So is there a way to detect if data is still loading?
Like for example, I need data to load from A1:A100, but data has only populated until A50. In this case I need to know that the other 50 cells are still populating.
View 9 Replies
View Related
Jan 5, 2009
refer to the picture:
Column H and column X contain dates. If column H = column X, "OK" will be shown. Otherwise "CHECK" would be shown.
The dates are the same, but the problem is, Excel reads column X differently from column H.
I've tried converting both columns to ddmmyyyy and then comparing them, but it doesn't work.
For i = 2 To range("A" & rows.count).end(xlup).row
If Format(Cells(i, 24), "ddmmyyyy") = Format(Cells(i, 8), "ddmmyyyy") Then
Cells(i, 36) = "OK"
Else
Cells(i, 36) = "CHECK"
End If
Next
Is there a foolproof way of doing it?
Column X is data retrieved from Bloomberg, so the format cannot be changed directly.
View 9 Replies
View Related
Aug 5, 2007
If it is actually not possible to change the way that bloomberg exports the information to excel then i think there might be two potential ways around it...neither of which i am sure how to do...
1) write some sort of macro which takes data from the new workbook, pastes it into the active workbook then closes the newly created workbook. Potential problem here is that whenever it creates a new workbook it has a slightly different name. eg. "abc1" then "abc2" etc.
2) Write some sort of macro which gets a list of companies from bloomberg with upcoming events over the next week, put this into the active workbook and then simply use the bloomberg get functions to fill out the info.
View 2 Replies
View Related
Mar 16, 2009
Is it possible to run a macro (concatenate) automatically on a sheet when data is refreshed on that sheet from access database? It there a code that has to be written in vb?
View 5 Replies
View Related
Mar 14, 2008
I see some code on the forum on how to automatically refresh pivot tables in excel. I know to press alt + f11, but where do I go from there to put the code in? Also, what if I have several pivot tables in the spreadsheet, is there anyway to refresh all of them automatically?
View 9 Replies
View Related
Jul 27, 2006
i have an excel spreadsheet with 27 or so workeets. it contains sales figures in it.
I want to be able to link mutiple cells of this workbook to another workbook so that it retreives that data, So that when I hit the refresh (!) button it will automatically put the data in. I will recieve new sales figures (new files) on a monthly basis so i want it to be able to update the figures to the new figures.
View 4 Replies
View Related
Aug 20, 2013
I have 6 pivot charts within a document. I use a macro associated with a button to refresh the tables and therefore the data displayed on the charts.
However, each time I refresh the data, the 'data label' font size also refreshes. What is particularly odd is that 5 of the charts are pie-graphs, of those 5 graphs, the font size only resets on 4 of them (with the data labels in a single chart staying at my desired font size).
I can manually put the font size back to that which I desire but I'd rather it just didn't change in the first place.
I've had a look to see if there's any noticeable differences between the 5 charts but there's nothing which I can find.
I'd be happy to run a bit of VBA to set the font size as I want it, if there isn't a better solution.
View 1 Replies
View Related
Jan 4, 2008
I have a query with ODBC connection to a SQL database. This query's parameter is linked to a cell. The resultant data is the source for a pivot table. I want to refresh the pivot table, when the query is run. I've tried using the cell that triggers the query....but the problem is that the query takes about 10 seconds to run. By the time the query returns new data....the pivot has already refreshed. I need it to refresh AFTER the query is complete.
I tried adding a cell that sums up the data from the query...thinking when THAT changes (due to updated data), to trigger the pivot refresh. Problem is that I don't know the trigger for when the sum cell changes (ie....formula change, not typed in.)
View 9 Replies
View Related
Mar 4, 2009
I have Excel file that is linked to Access Database. Everytime the user opens the file, there is pop up message that warns the user we there to enable the macro or to disable it. I want to force the user to enable the macro or even better if I can make disappear this warning and automatically enable the macro. See attached for more details.
View 3 Replies
View Related
Mar 22, 2012
I have this running and Section A is working but Section B is not. I basically want it so that if conditions a, b, and c occur, excel pops up with that messagebox.
Condition A: Cells(8, 3) changes via formula referencing an outside cell
Condition B: The new value of Cells(8,3) is a numeric value.
Condition C: The change in it's value is not more than a given threshold
change as defined in Cells(8, 13)
why the second section is not popping up with a msgbox when the value of Cells(8, 3) changes.
Public bbb as long
Sub Other()
bbb = Cells(8, 3)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim bb As Integer
Dim cc As Integer
[code]....
View 4 Replies
View Related
Dec 17, 2009
I've created a file where our accountmanagers can mace price calculations. But eversince i've added a macro to force them to fill the form completely, they have gone and tried to hack the macro (and with succes).
I now would like to add a protection for the macro, so they can only access VBA when "sheet1" is unprotected. How can i do this?
I also would like to create a macro so they only can save the file as a Macro Enabled Excel file (.xlsm),
how can i do this best? .....
View 9 Replies
View Related
Aug 6, 2007
I have a sheet which is protected but people are still able to change cell formats etc if they use the standard paste method. I want people to be able to paste data in but it forces paste special values to stop people pasting formats etc over already formatted and locked cells.
I know I could write a macro and run it using the shortcut keys ctrl-v but then the users lose the ability to undo if they make a mistake.
Is there a way to force paste special or allow users to run a macro but be able to do undo if they make a mistake?
View 9 Replies
View Related
Sep 12, 2009
I have a spreadsheet that has a button named Exit Requisition Program on it to close the application with. When the button is used everything is wonderful. All of the data on the sheet is saved and the application closes. I have a problem with some users clicking the red X without saving changes and that creates a big problem.
What I need to do is any one of several things.
1. Remove the red X completely.
2. Deactivate the red X.
3. Have a message box pop up instructing the user to use the Exit Button when the red X is used. Any one of the three methods listed above would work for me.
View 2 Replies
View Related
Apr 30, 2014
Trying to create a macro where it will begin by inserting today's date into a cell, then pause for roughly 35 seconds while the massive correlation matrix pulls all the correlations of the portfolio in, then takes an output value that is generated in the workbook from the filled correlation matrix (correlation coefficient) and copy's and pastes it in a different cell. Then it repeats this process again using "Today's date - 1" (yesterday's date), waiting 35 seconds again and taking the new output value and copy and pasting it below the previous one.
I know I am not close but here is what I have for code thus far (which does not work properly).
[Code].....
View 4 Replies
View Related
Jul 9, 2009
I have a problem with this this code trying to use BLP_DATA_CTRLLib.BlpData.
Sub MultidayHistory()
Dim objDataControl As BLP_DATA_CTRLLib.BlpData
Set objDataControl = New BlpData
Debug.Print "Formulaire Ouvert"
Dim vtResult() As Variant
Dim arrayFields As Variant
Dim vtSecurities() As Variant
Dim CalculDate As Date ' rien à voir avec cdate
Startd = Range("C10").Value
Endd = Range("C11").Value
arrayFields = Array("PX_LAST", "PX_VOLUME", "SPOT_PRICE", "CHG_NET_1D", "CHG_PCT_1D")
arraySecurities = Array("GLE FP Equity", "SX5P Index")......................
View 2 Replies
View Related
Feb 18, 2010
I have been using excel for years but have always managed to resolve issues using formula's etc but now i have had to step into the realms of macro's. I am slowly learning but urgently need help with the below problem which is bound to be a simple solution.
I have compiled a workbook for users to administrate on a daily basis. (each worksheet is a different day of the month but the same template) There are a few hidden worksheets as the info is extracted from these to run reports.
The issue/s i have is that administrators use cut, copy and paste to repeat data opposed to retyping, this messes formats, formulas and validation up. I have used a macro from the net to remove this option and it works fine. Problem is that if they do not enable macro's this will cease to be an option.
I understand that you cannot remove the option of enabling macro's but you can force. Again used a macro from the web that if you don't enable it only shows a welcome page saying that macro's need to be enabled to use. (all others are hidden) if macro's are enabled the welcome page disappears and the worksheets 1-31 reappear. Again this worked fine.
I tried putting both macro's into the same workbook but kept coming up with errors, each one i resolved led to another.
So short of it i need a instructions/macro to force macros and remove cut copy and paste.
View 14 Replies
View Related
Nov 27, 2008
I've recently got into using excel for more complicated work but still have a less than basic knowledge as I'm attempting to teach myself through trial and error. What I'm currently working on is a simple macro that downloads data from a website (which I've done) but ideally I'd like to have this macro recreate the data roughly every 30 seconds/minute and also have this data recorded next to the existing data rather than on top of it. I realise that you can import data from the web using the standard tools and can set it to reset every 60 seconds but I don't think you can get it to reload the data next to it which is why I was considering a macro.
View 10 Replies
View Related
Oct 16, 2012
I need to add a Bloomberg BDP function to my lookup statement. The formula that i'm trying to replicate in VBA is =IF(ISBLANK(h23,"",BDP(H23&" cusip","long_comp_name")). update my macro.
View 9 Replies
View Related
Apr 30, 2014
I have an excel workbook right now with a 75x75 correlation matrix. It has 75 stock prices and uses bloomberg to pull in the correlation between each of the holdings. There is a lot of data so it takes roughly 35 seconds for it all to get pulled in from bloomberg. The input value that I can change is the date. The output value is a specific correlation coefficient. Thus, if I input today's date into a cell in the file, it will run the correlation matrix and tell me the correlation between all the holdings using the past year's data. Then it will produce a single output value.
I am now looking to use VBA to make this more robust. What I have been trying to do is have a macro which will -
(1) input the current date into the input cell
(2) have the code wait 35 seconds (and since it is using bloomberg pull in the traditional wait methods do not work because they actually stop the data pull in also)
(3) copy the output value
(4) paste special that value in a different cell
(5) repeat the process but this time use the date from a day before and have the output value copied and pasted to a cell below the previous one.
This loop would continue over 20 times.
Below I have some code that allows me to do this process once but is not looped and I have been stuck trying to make this work with a loop given the bloomberg delay. When I add a loop, it does not wait the 35 seconds I need before doing the whole process again.
[Code] .....
View 2 Replies
View Related
Aug 21, 2007
I've been searching but I can't find an answer that fits. Here's the closest I've come. Force Date Entry on Particular Condition. What I need is if A10 has any data in it then I want a box to pop up that instructs the user to input data into B10 they click OK and then enter the data. Once the user inputs data into B10 then the error should be cleared. If they don't enter data then the error should keep coming back.
View 3 Replies
View Related
Apr 23, 2014
I have a macro which:
Opens up a workbook
Presses refresh all
Waits 10 seconds
Selects 1 cell
Saves the workbook
Closes the workbook
At some point in this process a dialog box comes up saying 'this action will cancel a pending refresh command' or words like that. How I can bypass this? It interrupts the macro.
View 14 Replies
View Related
Jul 15, 2014
I want to put a macro in that will refresh the workbook before this next part of code is run:
[Code] .....
I tried putting this in before:
[Code] ....
But it isn't refreshing the workbook before the next part is run
View 2 Replies
View Related
Jun 16, 2008
I am trying to write a creative procedure that on workbook open will check the name of the worksheet if it includes the day's date in sheetname & if it doesn't rename the sheet & delete current region from A1, else exit the sub.
Then runs a query on an Access DB to place in the above worksheet.
I need this due to novice XL & DB (8)users.
This is what I have at the moment on a test DB courtesy of J Walkenbach's sample files .....
View 9 Replies
View Related
May 22, 2006
My Excel spreadsheet is linked to an Access database to Get external Data. When the data is periodically refreshed ,while the spreadsheet is open, I need to automatically detect it so that a macro can be run each time to format the new data.
The Excel data is not a pivot table. The spreadsheet is for display of data only (no user updating).
View 5 Replies
View Related
Oct 24, 2006
writing a macro that will refresh all the active cells in my "Master" worksheet.
Every month, I append data from one Excel worksheet to the end of my Master worksheet. I want all the cells in the Master worksheet to be formatted as text. However when I copy the cells, I get different formats, i.e. text, general, number, etc. Simply selecting the new data and applying the text format from the Format Cells popup, does not work. I know I need to "re-enter" all the data for the new format to take effect but I really don't want to F2-> Enter thousands of cells.
how to write a macro that will refresh all active cells in a worksheet?
View 4 Replies
View Related
