For experienced macro users this will looks easy but unfortunately for me its a daunting task I have to retrieve data from an online source and use this data to make calculations in Calc. I am thinking using a macro but I have ZERO experience with this, nor with Visual Basic or anything of that sort…
Basically what I have to do (step by step):
I have a list of stock market tickers stored in a column in my spreadsheet. The macro should retrieve the first ticker in that column, append it to an URL and use that URL to retrieve a CSV file. In BASH (linux), I’d use a “for loop” with wget…
The macro should temporarily store the CSV to do calculations, then flush it. Either in the same spreadsheet (in a separate temporary tab) or by creating another ODS file then destroying it.
The macro should lookup values from the imported data, and map it to the corresponding cell in the tab where the tickers are stored. For example, if the first ticker is “GOOG” and the 6th column right to the ticker column is for “Net Income”, then the macro should look for “Net Income” in the imported CSV data, then copy the value found in the 6th cell to the right of the ticker. In Calc, I’d use a VLOOKUP if I was manually entering data…
Flush the imported data (destroy the temporary tab or file)
Repeat for the next ticker
Continue until all tickers are done.
I managed to dig in this forum and found a macro kindly provided by a user to scan a folder on the computer then import all found CSV files to ODS files, but this is not quite what I am looking to do. Plus, the macro doesnt handle data.
Sub collectCSV Const FILTER_NAME = "Text - txt - csv (StarCalc)" Const FILTER_OPTIONS = "59,34,33,0,,0,false,false,true,false,false" Dim sPath As String, sFileName As String, sSheetName As String Dim oDoc As Variant, oSheets As Variant, oSheet As Variant GlobalScope.BasicLibraries.LoadLibrary("Tools") oDoc = CreateNewDocument("scalc") oSheets = oDoc.getSheets() sPath = CurDir sPath = InputBox ("Please enter a folder name:","Source folder", sPath) sFileName = Dir(sPath + getPathSeparator + "*.csv",0) Do sSheetName = CheckNewSheetname(oSheets, _ GetFileNameWithoutExtension(sFileName, getPathSeparator)) oSheets.insertNewByName(sSheetName,0) oSheet = oSheets.getByName(sSheetName) oSheet.link(ConvertToURL(sPath + getPathSeparator + sFileName), _ sSheetName, FILTER_NAME, FILTER_OPTIONS, _ com.sun.star.sheet.SheetLinkMode.VALUE) oSheet.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE) sFileName = Dir() Loop Until sFileName = "" End Sub
Are macros adequate to do what I want to do? Thank you!