Data Retrieved From Bloomberg
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
ADVERTISEMENT
Feb 4, 2009
I have the following data retrieved via import data tools ...
View 9 Replies
View Related
Jan 16, 2007
I have a vba macro written in an excel worksheet (WS1) that reads another excel file (WS2) and retrieves data and writes it into the worksheet with the macro (WS1). It works beauifully except for one little problem. When I send it to a co-worker the numbers part of the data returns null values. I am connecting to (WS2) using ADO connection strings for the spreadsheet and recordset. The only difference I see is that in references of WS1, it shows Microsoft ActiveX Data Objects 2.7 library, it shows 2.8 library on my machine. Also it shows recordset 2.8 library on my coworkers machine, as well as mine.
View 3 Replies
View Related
Jan 25, 2009
I can still do a pivot report against the 'saved' data. So it must be out there in a file...? I read the function GETPIVOTDATA. Based on the description, It sounds like it would work to rebuild the source data. However the examples given don't see to match up with that. I have tried playing with the command, but I really don't know what I am doing and have had no luck
Does anyone know of a way to retrieve that stored data that the pivot reports are reporting against?
View 8 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
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
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
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
View Related
Aug 1, 2014
I have a list of items in column A. Column B has each item's net price. Columns C-N shows the consumption of the items per month. Column O shows in which location the items are stored.
I need to do a sumproduct so that it shows the value of the items retrieved from that particular location per month.
If it didn't have to be by location, I would've simply done something like =sumproduct($B1:$B10,C1:C10) and copied it across the columns. How do I tell excel to sum per location as well? I know there is a simple solution to this, just not seeing it...
EDIT: I tried =SUMPRODUCT(($B1:$B10)*(C1:C10)*(O1:O10=$B1)) but it gives a #VALUE error
View 9 Replies
View Related
May 22, 2012
I have a drop down (look up list) labeled Equipment that retrieves information from another sheet in my workbook.
I have a second field labeled Equipment Sub Type where I need a drop down list based on the information retrieved in the Equipment Field.
Each item in the Equipment List Drop Down has a corresponding list in my lookup sheet of my workbook.
View 3 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
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, 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
Apr 15, 2014
, just by using the ISIN code of a Bond in bloomberg, to get in excel:
- Coupon
- Cpn Freq
- Day Cnt
- Maturity
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
Apr 28, 2009
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
View 4 Replies
View Related
Dec 6, 2006
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
View 2 Replies
View Related
Jan 29, 2013
I am trying to create a user form that will allow the user to type int values in boxes. Then when the user clicks the submit button the code needs to add the values from each user input box to the existing values in specific cells accross multiple worksheets. Then the form needs to be cleared after the cell values are updated. I can create the form it is the code on the submit button i am lost on. Also it is important that some boxes may be left blank.
If you click the Grey "Qty Form" button on sheet1 the form will open. User data numbers can be entered in the blank boxes. When the submit button is pressed the form needs to add the user entered numbers to the numbers in the corresponding cells in sheet 1 and sheet 2. How to code the submit button to do this properly. Also after the data on the spreadsheet is updated the form needs to be cleared and start the cursor back in the Item 1 box on the form.
View 1 Replies
View Related
Dec 17, 2013
- I have total of 13 sheets in a workbook - 12 sheets represent 12 months with data; 13th sheet is single sheet in which i would like to get complete overview of 12 months
- each of 12 sheets has actually sales results for multiple products with following data: internal code, manufacturer part number, name, and qty sold in that month
- situation is that some products have been phased out during the year and some were introduced so each sheet is slightly different in terms of in which row certain product is located
What i would like to achive is to make 13th sheet (whole year overview) do the following:
- there is a list of all the products in it, each product has unique internal code - this code (from each line) should be used to find that code in each monthly sheet, then find its monthly sales value (copy it) and paste it in sheet 13 in cell that represents this product and particular month.
In other words i would like to see for each product what was monthly sales throughout this year, but avoid manually filling in qty for each product per month.
View 2 Replies
View Related
Jun 30, 2014
I have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.
The data ref will be column F which is the different event locations.
I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.
I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.
The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.
View 5 Replies
View Related
Apr 30, 2014
I am attempting to create a macro to generate emails based on data in a sheet. The goal is to run the Macro, and have it generate emails to send to contractors letting them know what they are going to be paid. For instance:
Name in Column J
Email in Column L
Memo in Column N
Balance in Column T
Due Date in Column P
Week Ending Date in Column H
Now what I would like to happen, is to tie a macro into a button that will create the email as follows:
To Field: Email address from Column L
Subject: "Company Payment Remittance Payment Date *Date from Column P*"
Body: Hello *Name from Column J*,
For *WE Date in Column H* you will be paid *Balance from Column T* for the time worked of *Memo in Column N*
Now the tricky part is that I want the email to contain all line items for each email address. So instead of sending one email per line, have the macro automatically put all of the information that needs to be sent to one email address into the message. I don't know if that is possible, but it sure would make my life easier if it was.
I have attached a sample workbook of the data that will be used
Example Workbook for Email Macro.xlsx
View 1 Replies
View Related
Feb 8, 2014
I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...
First I was using a msgbox function to get the info:
HTML Code:Â
For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b
But it can be up to 30 different new employees... and that is time consuming.
I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....
HTML Code:Â
Private Sub CloseButton_Click()
Unload UserForm1
End Sub
Private Sub ComboBox1_Change()
[Code] ......
View 2 Replies
View Related
Sep 27, 2011
I am trying to create a line graph that will incorporate multiple columns of data in one series of data. The reason I do not place all of the data in one column is because it could exceed the maximum amount of rows allowed in excel. Also I need the data split up for viewing purposes.
I can easily just graph one column but how do I combine all the columns into one line graph with the data being in separate columns. Basically all the columns will be my Y values and X values are just 1:n.
Example Below:
Column AColumn B Column C159261037114812
Now in the example all of the values are x values.
View 2 Replies
View Related
Jul 4, 2012
I have a workbook that includes 4 seperate sheets that are used to record time and expenses for 4 members of staff. I want to write a macro to select the data I need from each sheet and colaberate together in a 'data' sheet so I can combine all the info to run time and expense reports per client showing combination of all time and expense incurred from all 4 staff.
I have named cell ranges in each of the 4 time-sheets. I proceed to record a macro, select the first named range, copy and paste into my data sheet, do a control home then control down arrow, then one more down arrow to get to the first blank cell and repeat the process for all four time-sheets.
This works until I add a new line and then the data will only appear for the last time-sheet (last row of data).
View 2 Replies
View Related
Oct 19, 2012
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
View 2 Replies
View Related
Jan 11, 2013
I have created a pivot table that is connected to an input sheet with data. The input sheet retrieves data automatically from a external source through an add-in to Excel. When updating data the fields expands, but only for the items which have been changed. I want the table to be updated automatically, but not the fields expand automatically. Is there any pivot options to prevent this problem?
It should be mentioned that the pivot table is not directly connected to the input sheet (which is updated from the external source), but from a "help-sheet" reflecting the input sheet with some additional columns. I use conditional formatting and name range in the pivot.
View 1 Replies
View Related
Apr 22, 2014
Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
I am using Excel 2010
View 2 Replies
View Related
Jul 15, 2014
I have a table in the format below with about 3500 rows
Column A
Column B
0001
All vehicles, Retirements
0002
All vehicles, Retirements, Addition
0003
All vehicles, Retirements, Addition, Deletion from Y
I would like to change it to the following format:
Column A
Column B
0001
All vehicles
0001
Retirements
0002
All vehicles
0002
Retirements
0002
Addition
0003
All vehicles
0003
Retirements
0003
Addition
0003
Deletion from Y
View 3 Replies
View Related
