API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 21st, 2017
445
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 17.91 KB | None | 0 0
raw download clone embed print report
  1. diter = 0
  2. field = "px_last"
  3. For Each d In dates
  4. diter = diter + 1
  5.  
  6. For s = 1 To numb_sec
  7.  
  8. bbticker = securities(s)
  9. wsSec.Range(cl & diter).Formula = _
  10. "=BDH(""" & bbticker & """,""" & field & """,""" & d & """,""" & d & """)"
  11.  
  12. wsSec.Calculate
  13.  
  14.  
  15. Next s
  16. Next d
  17.  
  18. Application.OnTime Now + TimeValue("00:00:01"), "NextFunction"
  19.  
  20. ' disable events
  21. Application.EnableEvents = False
  22.  
  23. ' your code etc (but don't calculate)
  24. diter = 0
  25. field = "px_last"
  26. For Each d In dates
  27. diter = diter + 1
  28. For s = 1 To numb_sec
  29. bbticker = securities(s)
  30. wsSec.Range(cl & diter).Formula = _
  31. "=BDH(""" & bbticker & """,""" & field & """,""" & d & """,""" & d & """)"
  32. Next s
  33. Next d
  34.  
  35. ' re-enable events
  36. Application.EnableEvents = True
  37.  
  38. ' don't just calculate the sheet - call Application.Calculate
  39. Application.Calculate
  40.  
  41. ' wait till calculation complete
  42. ' https://stackoverflow.com/questions/11277034/wait-until-application-calculate-has-finished
  43. If Not Application.CalculationState = xlDone Then
  44. DoEvents
  45. End If
  46.  
  47. ' do save etc
  48. ' code...
  49.  
  50. 'Samir Khan
  51. 'simulationconsultant@gmail.com
  52. 'The latest version of this spreadsheet can be downloaded from http://investexcel.net/multiple-stock-quote-downloader-for-excel/
  53. 'Please link to http://investexcel.net if you like this spreadsheet
  54.  
  55.  
  56. Sub DownloadStockQuotes(ByVal stockTicker As String, ByVal StartDate As Date, ByVal EndDate As Date, ByVal DestinationCell As String, ByVal freq As String)
  57.  
  58. Dim qurl As String
  59. Dim StartMonth, StartDay, StartYear, EndMonth, EndDay, EndYear As String
  60.  
  61. qurl = "http://finance.google.com/finance/historical?q=" & stockTicker
  62. qurl = qurl & "&startdate=" & MonthName(Month(StartDate), True) & _
  63. "+" & Day(StartDate) & "+" & Year(StartDate) & _
  64. "&enddate=" & MonthName(Month(EndDate), True) & _
  65. "+" & Day(EndDate) & "+" & Year(EndDate) & "&output=csv"
  66.  
  67. On Error GoTo ErrorHandler:
  68.  
  69. QueryQuote:
  70. With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Range(DestinationCell))
  71. .BackgroundQuery = True
  72. .TablesOnlyFromHTML = False
  73. .Refresh BackgroundQuery:=False
  74. .SaveData = True
  75. End With
  76.  
  77. ErrorHandler:
  78.  
  79. End Sub
  80.  
  81. Sub DownloadData()
  82.  
  83. Dim frequency As String
  84. Dim numRows As Integer
  85. Dim lastRow As Integer
  86. Dim lastErrorRow As Integer
  87. Dim lastSuccessRow As Integer
  88. Dim stockTicker As String
  89. Dim numStockErrors As Integer
  90. Dim numStockSuccess As Integer
  91.  
  92. numStockErrors = 0
  93. numStockSuccess = 0
  94.  
  95. Application.Calculation = xlCalculationManual
  96. Application.ScreenUpdating = False
  97.  
  98. lastErrorRow = ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row
  99. lastSuccessRow = ActiveSheet.Cells(Rows.Count, "L").End(xlUp).Row
  100.  
  101. ClearErrorList lastErrorRow
  102. ClearSuccessList lastSuccessRow
  103.  
  104. lastRow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).Row
  105. frequency = Worksheets("Parameters").Range("b7")
  106.  
  107. 'Delete all sheets apart from Parameters sheet
  108. Dim ws As Worksheet
  109. Application.DisplayAlerts = False
  110. For Each ws In Worksheets
  111. If ws.Name <> "Parameters" And ws.Name <> "About" Then ws.Delete
  112. Next
  113.  
  114. Application.DisplayAlerts = True
  115.  
  116. 'Loop through all tickers
  117. For ticker = 12 To lastRow
  118.  
  119. stockTicker = Worksheets("Parameters").Range("$a$" & ticker)
  120.  
  121. If stockTicker = "" Then
  122. GoTo NextIteration
  123. End If
  124.  
  125. Sheets.Add After:=Sheets(Sheets.Count)
  126.  
  127. If InStr(stockTicker, ":") > 0 Then
  128. ActiveSheet.Name = Replace(stockTicker, ":", "")
  129. Else
  130. ActiveSheet.Name = stockTicker
  131. End If
  132.  
  133. Cells(1, 1) = "Stock Quotes for " & stockTicker
  134. Call DownloadStockQuotes(stockTicker, Worksheets("Parameters").Range("$b$5"), Worksheets("Parameters").Range("$b$6"), "$a$2", frequency)
  135. Columns("a:a").TextToColumns Destination:=Range("a1"), DataType:=xlDelimited, _
  136. TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
  137. Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1))
  138.  
  139.  
  140. If InStr(stockTicker, ":") > 0 Then
  141. stockTicker = Replace(stockTicker, ":", "")
  142. End If
  143.  
  144. Sheets(stockTicker).Columns("A:G").ColumnWidth = 10
  145.  
  146. lastRow = Sheets(stockTicker).UsedRange.Row - 2 + Sheets(stockTicker).UsedRange.Rows.Count
  147.  
  148. If lastRow < 3 Then
  149. Application.DisplayAlerts = False
  150. Sheets(stockTicker).Delete
  151. numStockErrors = numStockErrors + 1
  152. ErrorList stockTicker, numStockErrors
  153. GoTo NextIteration
  154. Application.DisplayAlerts = True
  155. Else
  156. numStockSuccess = numStockSuccess + 1
  157. If Left(stockTicker, 1) = "^" Then
  158. SuccessList Replace(stockTicker, "^", ""), numStockSuccess
  159. Else
  160. SuccessList stockTicker, numStockSuccess
  161. End If
  162. End If
  163.  
  164. Sheets(stockTicker).Sort.SortFields.Add Key:=Range("A3:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  165. With Sheets(stockTicker).Sort
  166. .SetRange Range("A2:G" & lastRow)
  167. .Header = xlYes
  168. .MatchCase = False
  169. .Orientation = xlTopToBottom
  170. .SortMethod = xlPinYin
  171. .Apply
  172. End With
  173.  
  174. Range("a3:a" & lastRow).NumberFormat = "yyyy-mm-dd;@"
  175.  
  176. 'Delete final blank row otherwise will get ,,,, at bottom of CSV
  177. Sheets(stockTicker).Rows(lastRow + 1 & ":" & Sheets(stockTicker).Rows.Count).Delete
  178.  
  179. 'Remove initial ^ in ticker names from Sheets
  180. If Left(stockTicker, 1) = "^" Then
  181. ActiveSheet.Name = Replace(stockTicker, "^", "")
  182. Else
  183. ActiveSheet.Name = stockTicker
  184. End If
  185.  
  186. 'Remove hyphens in ticker names from Sheet names, otherwise error in collation
  187. If InStr(stockTicker, "-") > 0 Then
  188. ActiveSheet.Name = Replace(stockTicker, "-", "")
  189. End If
  190.  
  191.  
  192. NextIteration:
  193. Next ticker
  194.  
  195. Application.DisplayAlerts = False
  196.  
  197. If Sheets("Parameters").Shapes("WriteToCSVCheckBox").ControlFormat.Value = xlOn Then
  198. On Error GoTo ErrorHandler:
  199. Call CopyToCSV
  200. End If
  201.  
  202. If Sheets("Parameters").Shapes("CollateDataCheckBox").ControlFormat.Value = xlOn Then
  203. On Error GoTo ErrorHandler:
  204. Call CollateData
  205. End If
  206.  
  207. ErrorHandler:
  208.  
  209. Worksheets("Parameters").Select
  210.  
  211. Application.ScreenUpdating = True
  212. Application.Calculation = xlCalculationAutomatic
  213.  
  214. Worksheets("Parameters").Select
  215. For Each C In ThisWorkbook.Connections
  216. C.Delete
  217. Next
  218.  
  219. End Sub
  220. Sub CollateData()
  221.  
  222. Dim ws As Worksheet
  223. Dim i As Integer, first As Integer
  224. Dim maxRow As Integer
  225. Dim maxTickerWS As Worksheet
  226.  
  227. maxRow = 0
  228. For Each ws In Worksheets
  229. If ws.Name <> "Parameters" Then
  230. If ws.UsedRange.Rows.Count > maxRow Then
  231. maxRow = ws.UsedRange.Rows.Count
  232. Set maxTickerWS = ws
  233. End If
  234. End If
  235. Next
  236.  
  237. Sheets.Add After:=Sheets(Sheets.Count)
  238. ActiveSheet.Name = "Open"
  239.  
  240. Sheets.Add After:=Sheets(Sheets.Count)
  241. ActiveSheet.Name = "High"
  242.  
  243. Sheets.Add After:=Sheets(Sheets.Count)
  244. ActiveSheet.Name = "Low"
  245.  
  246. Sheets.Add After:=Sheets(Sheets.Count)
  247. ActiveSheet.Name = "Close"
  248.  
  249. Sheets.Add After:=Sheets(Sheets.Count)
  250. ActiveSheet.Name = "Volume"
  251.  
  252. Sheets.Add After:=Sheets(Sheets.Count)
  253. ActiveSheet.Name = "Adjusted Close"
  254.  
  255. i = 1
  256. maxTickerWS.Range("A2", "B" & maxRow).Copy Destination:=Sheets("Open").Cells(1, i)
  257. Sheets("Open").Cells(1, i + 1) = maxTickerWS.Name
  258.  
  259. maxTickerWS.Range("A2", "a" & maxRow).Copy Destination:=Sheets("High").Cells(1, i)
  260. maxTickerWS.Range("c2", "c" & maxRow).Copy Destination:=Sheets("High").Cells(1, i + 1)
  261. Sheets("High").Cells(1, i + 1) = maxTickerWS.Name
  262.  
  263. maxTickerWS.Range("A2", "a" & maxRow).Copy Destination:=Sheets("Low").Cells(1, i)
  264. maxTickerWS.Range("d2", "d" & maxRow).Copy Destination:=Sheets("Low").Cells(1, i + 1)
  265. Sheets("Low").Cells(1, i + 1) = maxTickerWS.Name
  266.  
  267. maxTickerWS.Range("A2", "a" & maxRow).Copy Destination:=Sheets("Close").Cells(1, i)
  268. maxTickerWS.Range("e2", "e" & maxRow).Copy Destination:=Sheets("Close").Cells(1, i + 1)
  269. Sheets("Close").Cells(1, i + 1) = maxTickerWS.Name
  270.  
  271. maxTickerWS.Range("A2", "a" & maxRow).Copy Destination:=Sheets("Volume").Cells(1, i)
  272. maxTickerWS.Range("f2", "f" & maxRow).Copy Destination:=Sheets("Volume").Cells(1, i + 1)
  273. Sheets("Volume").Cells(1, i + 1) = maxTickerWS.Name
  274.  
  275. maxTickerWS.Range("A2", "a" & maxRow).Copy Destination:=Sheets("Adjusted Close").Cells(1, i)
  276. maxTickerWS.Range("g2", "g" & maxRow).Copy Destination:=Sheets("Adjusted Close").Cells(1, i + 1)
  277. Sheets("Adjusted Close").Cells(1, i + 1) = maxTickerWS.Name
  278.  
  279. i = i + 2
  280.  
  281. For Each ws In Worksheets
  282.  
  283. If ws.Name <> "Parameters" And ws.Name <> "About" And ws.Name <> maxTickerWS.Name And ws.Name <> "Open" And ws.Name <> "High" And ws.Name <> "Low" And ws.Name <> "Close" And ws.Name <> "Volume" And ws.Name <> "Adjusted Close" Then
  284.  
  285. Sheets("Open").Cells(1, i) = ws.Name
  286. Sheets("Open").Range(Sheets("Open").Cells(2, i), Sheets("Open").Cells(maxRow - 1, i)).Formula = _
  287. "=vlookup(A2," & ws.Name & "!A$2:G$" & maxRow & ",2,0)"
  288.  
  289. Sheets("High").Cells(1, i) = ws.Name
  290. Sheets("High").Range(Sheets("High").Cells(2, i), Sheets("High").Cells(maxRow - 1, i)).Formula = _
  291. "=vlookup(A2," & ws.Name & "!A$2:G$" & maxRow & ",3,0)"
  292.  
  293. Sheets("Low").Cells(1, i) = ws.Name
  294. Sheets("Low").Range(Sheets("Low").Cells(2, i), Sheets("Low").Cells(maxRow - 1, i)).Formula = _
  295. "=vlookup(A2," & ws.Name & "!A$2:G$" & maxRow & ",4,0)"
  296.  
  297. Sheets("Close").Cells(1, i) = ws.Name
  298. Sheets("Close").Range(Sheets("Close").Cells(2, i), Sheets("Close").Cells(maxRow - 1, i)).Formula = _
  299. "=vlookup(A2," & ws.Name & "!A$2:G$" & maxRow & ",5,0)"
  300.  
  301. Sheets("Volume").Cells(1, i) = ws.Name
  302. Sheets("Volume").Range(Sheets("Volume").Cells(2, i), Sheets("Volume").Cells(maxRow - 1, i)).Formula = _
  303. "=vlookup(A2," & ws.Name & "!A$2:G$" & maxRow & ",6,0)"
  304.  
  305. Sheets("Adjusted Close").Cells(1, i) = ws.Name
  306. Sheets("Adjusted Close").Range(Sheets("Adjusted Close").Cells(2, i), Sheets("Adjusted Close").Cells(maxRow - 1, i)).Formula = _
  307. "=vlookup(A2," & ws.Name & "!A$2:G$" & maxRow & ",7,0)"
  308.  
  309. i = i + 1
  310.  
  311. End If
  312. Next
  313.  
  314. On Error Resume Next
  315.  
  316. Sheets("Open").UsedRange.SpecialCells(xlFormulas, xlErrors).Clear
  317. Sheets("Close").UsedRange.SpecialCells(xlFormulas, xlErrors).Clear
  318. Sheets("High").UsedRange.SpecialCells(xlFormulas, xlErrors).Clear
  319. Sheets("Low").UsedRange.SpecialCells(xlFormulas, xlErrors).Clear
  320. Sheets("Volume").UsedRange.SpecialCells(xlFormulas, xlErrors).Clear
  321. Sheets("Adjusted Close").UsedRange.SpecialCells(xlFormulas, xlErrors).Clear
  322.  
  323. On Error GoTo 0
  324.  
  325. Sheets("Open").Columns("A:A").EntireColumn.AutoFit
  326. Sheets("High").Columns("A:A").EntireColumn.AutoFit
  327. Sheets("Low").Columns("A:A").EntireColumn.AutoFit
  328. Sheets("Close").Columns("A:A").EntireColumn.AutoFit
  329. Sheets("Volume").Columns("A:A").EntireColumn.AutoFit
  330. Sheets("Adjusted Close").Columns("A:A").EntireColumn.AutoFit
  331. End Sub
  332.  
  333. Sub SuccessList(ByVal stockTicker As String, ByVal numStockSuccess As Integer)
  334.  
  335. Sheets("Parameters").Range("L" & 10 + numStockSuccess) = stockTicker
  336.  
  337. Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlDiagonalDown).LineStyle = xlNone
  338. Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlDiagonalUp).LineStyle = xlNone
  339. Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeLeft).LineStyle = xlNone
  340. Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeTop).LineStyle = xlNone
  341. Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeBottom).LineStyle = xlNone
  342. Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeRight).LineStyle = xlNone
  343. Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlInsideVertical).LineStyle = xlNone
  344. Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlInsideHorizontal).LineStyle = xlNone
  345. Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlDiagonalDown).LineStyle = xlNone
  346. Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlDiagonalUp).LineStyle = xlNone
  347.  
  348. With Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeLeft)
  349. .LineStyle = xlContinuous
  350. .ColorIndex = 0
  351. .TintAndShade = 0
  352. .Weight = xlMedium
  353. End With
  354. With Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeTop)
  355. .LineStyle = xlContinuous
  356. .ColorIndex = 0
  357. .TintAndShade = 0
  358. .Weight = xlMedium
  359. End With
  360. With Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeBottom)
  361. .LineStyle = xlContinuous
  362. .ColorIndex = 0
  363. .TintAndShade = 0
  364. .Weight = xlMedium
  365. End With
  366. With Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeRight)
  367. .LineStyle = xlContinuous
  368. .ColorIndex = 0
  369. .TintAndShade = 0
  370. .Weight = xlMedium
  371. End With
  372.  
  373. Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlInsideVertical).LineStyle = xlNone
  374. Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlInsideHorizontal).LineStyle = xlNone
  375.  
  376. With Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Interior
  377. .PatternColorIndex = xlAutomatic
  378. .ThemeColor = xlThemeColorAccent2
  379. .TintAndShade = 0.799981688894314
  380. .PatternTintAndShade = 0
  381. End With
  382.  
  383. End Sub
  384.  
  385. Sub ErrorList(ByVal stockTicker As String, ByVal numStockErrors As Integer)
  386.  
  387. Sheets("Parameters").Range("J" & 10 + numStockErrors) = stockTicker
  388.  
  389. Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlDiagonalDown).LineStyle = xlNone
  390. Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlDiagonalUp).LineStyle = xlNone
  391. Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeLeft).LineStyle = xlNone
  392. Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeTop).LineStyle = xlNone
  393. Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeBottom).LineStyle = xlNone
  394. Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeRight).LineStyle = xlNone
  395. Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlInsideVertical).LineStyle = xlNone
  396. Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlInsideHorizontal).LineStyle = xlNone
  397. Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlDiagonalDown).LineStyle = xlNone
  398. Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlDiagonalUp).LineStyle = xlNone
  399.  
  400. With Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeLeft)
  401. .LineStyle = xlContinuous
  402. .ColorIndex = 0
  403. .TintAndShade = 0
  404. .Weight = xlMedium
  405. End With
  406. With Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeTop)
  407. .LineStyle = xlContinuous
  408. .ColorIndex = 0
  409. .TintAndShade = 0
  410. .Weight = xlMedium
  411. End With
  412. With Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeBottom)
  413. .LineStyle = xlContinuous
  414. .ColorIndex = 0
  415. .TintAndShade = 0
  416. .Weight = xlMedium
  417. End With
  418. With Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeRight)
  419. .LineStyle = xlContinuous
  420. .ColorIndex = 0
  421. .TintAndShade = 0
  422. .Weight = xlMedium
  423. End With
  424.  
  425. Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlInsideVertical).LineStyle = xlNone
  426. Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlInsideHorizontal).LineStyle = xlNone
  427.  
  428. With Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Interior
  429. .PatternColorIndex = xlAutomatic
  430. .ThemeColor = xlThemeColorAccent2
  431. .TintAndShade = 0.799981688894314
  432. .PatternTintAndShade = 0
  433. End With
  434.  
  435. End Sub
  436.  
  437. Sub ClearErrorList(ByVal lastErrorRow As Integer)
  438. If lastErrorRow > 10 Then
  439. Worksheets("Parameters").Range("J11:J" & lastErrorRow).Clear
  440. With Sheets("Parameters").Range("J10").Borders(xlEdgeLeft)
  441. .LineStyle = xlContinuous
  442. .ColorIndex = 0
  443. .TintAndShade = 0
  444. .Weight = xlMedium
  445. End With
  446. With Sheets("Parameters").Range("J10").Borders(xlEdgeTop)
  447. .LineStyle = xlContinuous
  448. .ColorIndex = 0
  449. .TintAndShade = 0
  450. .Weight = xlMedium
  451. End With
  452. With Sheets("Parameters").Range("J10").Borders(xlEdgeBottom)
  453. .LineStyle = xlContinuous
  454. .ColorIndex = 0
  455. .TintAndShade = 0
  456. .Weight = xlMedium
  457. End With
  458. With Sheets("Parameters").Range("J10").Borders(xlEdgeRight)
  459. .LineStyle = xlContinuous
  460. .ColorIndex = 0
  461. .TintAndShade = 0
  462. .Weight = xlMedium
  463. End With
  464. End If
  465. End Sub
  466.  
  467. Sub ClearSuccessList(ByVal lastSuccessRow As Integer)
  468. If lastSuccessRow > 10 Then
  469. Worksheets("Parameters").Range("L11:L" & lastSuccessRow).Clear
  470. With Sheets("Parameters").Range("L10").Borders(xlEdgeLeft)
  471. .LineStyle = xlContinuous
  472. .ColorIndex = 0
  473. .TintAndShade = 0
  474. .Weight = xlMedium
  475. End With
  476. With Sheets("Parameters").Range("L10").Borders(xlEdgeTop)
  477. .LineStyle = xlContinuous
  478. .ColorIndex = 0
  479. .TintAndShade = 0
  480. .Weight = xlMedium
  481. End With
  482. With Sheets("Parameters").Range("L10").Borders(xlEdgeBottom)
  483. .LineStyle = xlContinuous
  484. .ColorIndex = 0
  485. .TintAndShade = 0
  486. .Weight = xlMedium
  487. End With
  488. With Sheets("Parameters").Range("L10").Borders(xlEdgeRight)
  489. .LineStyle = xlContinuous
  490. .ColorIndex = 0
  491. .TintAndShade = 0
  492. .Weight = xlMedium
  493. End With
  494. End If
  495. End Sub
  496.  
  497.  
  498. Sub CopyToCSV()
  499.  
  500. Dim MyPath As String
  501. Dim MyFileName As String
  502.  
  503. dateFrom = Worksheets("Parameters").Range("$b$5")
  504. dateTo = Worksheets("Parameters").Range("$b$6")
  505. frequency = Worksheets("Parameters").Range("$b$7")
  506. MyPath = Worksheets("Parameters").Range("$b$8")
  507.  
  508. For Each ws In Worksheets
  509. If ws.Name <> "Parameters" And ws.Name <> "About" Then
  510. ticker = ws.Name
  511. MyFileName = ticker & " " & Format(dateFrom, "dd-mm-yyyy") & " - " & Format(dateTo, "dd-mm-yyyy") & " " & frequency
  512. If Not Right(MyPath, 1) = "" Then MyPath = MyPath & ""
  513. If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
  514. Sheets(ticker).Copy
  515. With ActiveWorkbook
  516. .SaveAs Filename:= _
  517. MyPath & MyFileName, _
  518. FileFormat:=xlCSV, _
  519. CreateBackup:=False
  520. .Close False
  521. End With
  522. End If
  523. Next
  524.  
  525. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!