Importing CSV and manipulating data with macro

[liovi2325]

Hello,

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!

[Ratslinger]

Hello,

This is a tough question to answer since some details are absent. For example, most of these stock market/bitcoin sights use an API returning JSON format and not CSV (have seen though). Another possibility is that in Calc you can link to HTML tables but haven’t seen this for stock market prices.

If in JSON, you may be able to utilize this extension (have not personally tested) → LibreOffice GetRest Plugin. If that is not acceptable you may need to have code written (probably in Python) to retrieve what is wanted. Did one small python macro and posted sample here → Importing BTC price to spreadsheet

For CSV it seems retrieval one item at a time is not typically the case. See this post for some basic information on Alphavantage → Alphavantage / GetYahoo API and Calc import. There you can get multiple CSV items in a single download. Also for that download, this may help → Import from REST API to Calc

Now all this is not going to give you an absolute answer. It does show there is more to this than what you have explained in the question. This also doesn’t even touch on the fact of how often this may be retrieved.

It would appear, however, that for someone without any macro experience this may be a task requiring a lot of education.

[liovi2325]

Hello Ratslinger, you are right, I missed some info. For example, I am using Morningstar’s API to retrieve the CSV files. For example, if you wanted to retrieve Google’s Income Statement, you would use the following URL:

http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=GOOG&reportType=is&period=12&dataType=A&order=asc&columnYear=5&number=3

See GitHub · Where software is built for more info on the API.

[liovi2325]

I also just realized that I failed to mention that I am NOT looking to retrieve last prices, but financial data (income statements, cash flow statements, balance sheets) which comes in table formats (hence CSV files).

[Ratslinger]

That is important to the question. Much more to absorb. Will look into later.

Seems on the surface that for desired automation you will need to code macro(s) to complete. This would be custom to what you are dealing with and not just some routine picked from any site.

[Ratslinger]

After further review, I cannot see how you will complete this in the automated fashion you want without knowing how to code macros.