Bits of Macro Code for Spreadsheets -thread

[JMN2]

I used to use Excel and its VBA, I mostly copied something close to what I needed, then modified it to fit my own needs. Now I use Libre Office Basic code, or try to...

I wonder if the following is possible, or if I could be given a push to right direction.

Two columns on a worksheet, ticker and yield. Another worksheet with a portfolio, some same tickers of course, but some duplicated (ISA and SIPP sections) and not in the same order necessarily (so simple vlookups functions don't seem to work). Macro would take a yield and place it onto the portfolio worksheet in the proper row and a certain column.

I have been trying to modify the one I use to get share prices from Yahoo which places a price a few columns right from the ticker. Thanks!




REM #####################################################################
REM ########### Start of macro to update price from Yahoo #########
REM #####################################################################
Sub UpdatePrice

DialogLibraries.loadLibrary("Standard")

oSheets = ThisComponent.Sheets
HYPsheet = oSheets.getByName("Portfolio")

REM check if yahoo sheet exists, and if not then create it
sheet_found = 0
for i = 0 to ThisComponent.Sheets.Count - 1
if ThisComponent.Sheets.getByIndex(i).Name = "YahooDownload" then
sheet_found = 1
exit for
end if
next i

if sheet_found = 0 then osheets.insertNewByName ("YahooDownload", osheets.getCOunt())

yahoo_sheet = osheets.getByName("YahooDownload")
ClearRange = yahoo_sheet.getCellRangeByName("A1:AZ1000")
ClearRange.clearContents(com.sun.star.sheet.CellFlags.STRING + com.sun.star.sheet.CellFlags.VALUE + _
com.sun.star.sheet.CellFlags.FORMULA + com.sun.star.sheet.CellFlags.OBJECTS)

HYPsheet.getCellRangeByName("E6:E1000").ClearContents(com.sun.star.sheet.CellFlags.VALUE)

url_middle = ""
for hyprow = 5 to 1000 step 1
if HYPsheet.getCellByPosition(2,hyprow).type = 0 then exit for

if left(HYPsheet.getCellByPosition(2,hyprow).string,1) = "^" then
url_middle = url_middle + HYPsheet.getCellByPosition(2,hyprow).string + "+"
else
url_middle = url_middle + HYPsheet.getCellByPosition(2,hyprow).string + ".l+"
end if

next hyprow


url_middle = Left(url_middle, Len(url_middle)-1)


sUrl = "http://quote.yahoo.com/d/quotes.csv?s=" +url_middle + "&f=sl1&e=.csv"
sFilter = "Text - txt - csv (StarCalc)"
sOptions = "44,34,SYSTEM,1,1/10/2/10/3/10/4/10/5/10/6/10/7/10/8/10/9/10"

Rem attempt to retrieve data up to 5 times
data_found = 0
for x = 0 to 5 step 1
yahoo_sheet.link(sUrl, "", sFilter, sOptions, com.sun.star.sheet.SheetLinkMode.NORMAL )
if yahoo_sheet.getcellbyposition(0,0).string <> "The link could not be updated." then
data_found = 1
exit for
end if
next x

if data_found = 0 then goto NO_DATA


Rem copy prices from Yahoo sheet to HYP sheet
for HYProw = 5 to 1000 step 1
if HYPsheet.getCellByPosition(2,hyprow).type = 0 then exit for
HYPsheet.getCellbyposition(4,HYProw).value = yahoo_sheet.getCellbyposition(1,Hyprow - 5).value
next HYProw


Rem set focus
Controller = ThisComponent.getcurrentController
Controller.setActiveSheet(HYPsheet)


REM remove yahoo download sheet
ThisComponent.getSheets().removeByName( "YahooDownload" )

if data_found = 1 then msgbox "Prices downloaded, Sir",,"Status"

NO_DATA:
if data_found = 0 then msgbox "Problem downloading prices, Sir, try again",,"Error"

End Sub

REM #####################################################################
REM ########### End of macro to update price from Yahoo ###########
REM #####################################################################

[djpeck1]

I would have thought that the LOOKUP function would have fulfilled your requirements in this example rather than resorting to using a macro/VBA code.

Let me know if you would like an example of this.

[djpeck1]

On second thoughts the VLOOKUP function would be a better choice (using the Range_lookup argument set to FALSE).

Your original post mentioned that you could not get this to work. What was the problem? Did you try with the Range_lookup argument set to FALSE?

[JMN2]

Thanks djpeck1, setting sort order to zero did the trick. Now I can just copy paste ticker and yield columns from elsewhere and my portfolio table will pick the yields up to its right column.

[djpeck1]

Thanks djpeck1, setting sort order to zero did the trick. Now I can just copy paste ticker and yield columns from elsewhere and my portfolio table will pick the yields up to its right column.

I'm glad that you got it all working.

If you need any VBA code to grab yield figures from the internet please let me know. I've written some code that grabs yield and other data from the MoneyAM website. I use it as part of my HYP analysis spreadsheet.

[MDW1954]

I'm not the original poster, but I'd be very interested in this. Another Fool pointed me in the direction of VBA data-scraping a couple of years ago, but real life got in the way in the shape of courses on R and analytics.

Having a working model to experiment with would be very useful.

MDW1954

[JMN2]

I wonder if for a LibreOffice there would be a bit of code:

when running yahoo share price update for the portfolio, if the portfolio value was higher than an existing one in a certain cell it would replace the old one, if not then it would not replace it. This way the cell would always show the highest ever max value. Currently I am sometimes wondering if my portfolio value has ever been this high in the past...

Yep, not too busy today...