Free send mail?

mikezx10 · #1 (**permalink**) 03-31-10, 06:37

I need to create a scheduled task that will open an excel file
Qry Bloomberg and then email the results. The computer is in my office but not part of the network. Is there a way to send emails with out outlook ?

If you can think of a better process I would also be interested. The bloomberg qry is for Fx Rates. Oh if you know of another way to get fx rates that would be even better

Thanks

kitaman · #2 (**permalink**) 03-31-10, 07:31

Install Microsoft Services for Unix, a free download from MS,
Download details: Windows Services for UNIX Version 3.5

Then use
mail -s "Subject" to@domain.com <text_file
where text_file is the output of your program.

mikezx10 · #3 (**permalink**) 03-31-10, 08:14

Microsoft Services for Unix for a computer running MS XP?

Colin Legg · #4 (**permalink**) 03-31-10, 10:08

I can help with the Bloomberg ---> Excel part... which FX rates do you need, and do you want to pull them in using formulas or VBA?

mikezx10 · #5 (**permalink**) 03-31-10, 11:10

CHF (CHF)
AUD (USDAUD)
GBP (USDGBP)

i would like to pull formulas & vba if possible (just in case)

Colin Legg · #6 (**permalink**) 03-31-10, 11:16

For my peace of mind, please can you also confirm that distributing proprietry Bloomberg data like this doesn't breach any Bloomberg licencing agreements?

Is that first rate USD/CHF?

mikezx10 · #7 (**permalink**) 03-31-10, 11:18

LOL the data is used in house for fx Gain loss accounting calc, yes the 1st is USD /CHF

Colin Legg · #8 (**permalink**) 03-31-10, 12:16

Which date(s)/time(s) do you want the rates from? COB yesterday?

mikezx10 · #9 (**permalink**) 03-31-10, 12:19

I would be running it every morning as of COB prior day. Are you in ny i think i owe u a beer for this effort LOL

Colin Legg · #10 (**permalink**) 03-31-10, 13:29

I'm in London, England, but I'd be happy to visit and buy you a beer if you cover my flights!

I've got Bloomberg at work so I'll post a solution tomorrow when I'm back in the office. I think there may be complications with the formula-based solution if you are automating the process.

kitaman · #11 (**permalink**) 03-31-10, 21:08

Quote:

Originally Posted by mikezx10

Microsoft Services for Unix for a computer running MS XP?

Yes, it installs like any other application, and gives you a KSH shell,and a CSH shell, along with the basic Unix utilities (sendmail, vi, grep) etc.

Colin Legg · #12 (**permalink**) 04-06-10, 10:57

Bloomberg -

First a formula approach.

In A1 put a date, say yesterday's date:

Code:

=TODAY()-1

In C3 put the currency ticker:

Code:

USDCHF Curncy

In C4 type in this field header (purely aesthetic):

Code:

Date

In D4 we need the field name to look up:

Code:

PX_LAST

Then in C5 the formula to retrieve the data:

Code:

=BDH($C$3,$D$4,$A$1,$A$1,"Dts=S","Quote=C","Days=A","DtFmt=D","cols=2;rows=1")

BDH is the Bloomberg Data History function.
PX_LAST is the last price on the date specified.
The DTS parameter defines whether or not to display the price's corresponding date ("S" is show). DtFmt formats the date output.
The Quote parameter defines whether to retrieve Average or Close price ("C" is close).
The Days parameter defines which days to observe. "A" is all calendar days.

If A1 contains a non-business day then it will retrieve the closing price from the previous business day. eg. For 4th April 2010 the price returned would be the closing price on 2nd April 2010.

Just repeat the set-up for each currency pair. The other tickers you need are:

Code:

AUDUSD Curncy
GBPUSD Curncy

I got all of this information using the import wizard in Excel under Bloomberg | Import Data. The Bloomberg Excel Tools add-in must be installed for the BDH() function to be recognised.

VBA solution to follow...

Colin Legg · #13 (**permalink**) 04-06-10, 13:40

It looks like there are a few different (but seemingly similar) methods available in the Bloomberg API to retrieve the data. There are bound to be caveats that I'm not aware of, but this works for me:

Code:

    'reference to the bloomberg data type library required 
 
Sub foo()
 
    Const strFIELD As String = "PX_LAST"
 
    Dim objBBG As BLP_DATA_CTRLLib.BlpData
    Dim vResults, vCurrencies
 
 
    vCurrencies = Array("USDCHF Curncy", "AUDUSD Curncy", "GBPUSD Curncy")
    Set objBBG = New BLP_DATA_CTRLLib.BlpData
    objBBG.AutoRelease = False
 
    vResults = objBBG.BLPGetHistoricalData(security:=vCurrencies, _
                                        fields:=strFIELD, _
                                        StartDate:=VBA.Date - 1, _
                                        EndDate:=VBA.Date - 1)
 
    'what did we find?
    Stop 'have a look at vResults in the locals window
 
 
    Set objBBG = Nothing
 
End Sub

For reasons *unknown*, one of the other calls might be better.

Internet Services

Web Development

Digital Marketing

Consumer Tech